3220. Odd and Even Transactions

Tables

erDiagram
	transactions {
		transaction_id int "Unique value"
		amount int
		transaction_date date
	}
Mermaid
복사

Question

Write a solution to find the sum of amounts for odd and even transactions for each day. If there are no odd or even transactions for a specific date, display as 0.

Return the result table ordered by transaction_date in ascending order.

The result format is in the following example.

Addtional Question

transaction_id에 따른 amount의 누적함

SELECT
	t1.transaction_id,
	t1.transaction_date,
	t1.amount,
	(
		SELECT
			SUM(t2.amount)
		FROM transactions t2
		WHERE t1.transaction_id >= t2.transaction_id
	) AS cumsum
FROM transactions t1
ORDER BY t1.transaction_id;
SQL
복사

SELECT
	t1.transaction_id,
	t1.transaction_date,
	t1.amount,
	SUM(amount) OVER(ORDER BY t1.transaction_id ASC) AS cumsum
FROM transactions t1;
SQL
복사

SELECT
	t1.transaction_id,
	t1.transaction_date,
	t1.amount,
	SUM(amount) OVER(ORDER BY t1.transaction_id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumsum
FROM transactions t1
SQL
복사

두 가지 모두 같은 갑을 출력하지만, 필터 혹은 조건을 추가해야하는 경우, subquery가 더 편리하다.

DataFlow

case when %2=0 -> odd, even -> groupby transaction_date -> sum(amount)

Answer

SELECT
	t2.transaction_date,
	SUM(odd) AS odd_sum,
	SUM(even) AS even_sum
FROM (
	SELECT
		t1.transaction_id,
		t1.transaction_date,
		t1.amount,
		CASE WHEN t1.amount % 2 = 0 THEN t1.amount ELSE 0 END AS even,
		CASE WHEN t1.amount % 2 = 0 THEN 0 ELSE t1.amount END AS odd
	FROM transactions t1
) t2
GROUP BY t2.transaction_date
ORDER BY t2.transaction_date ASC;
SQL
복사

SELECT
	transaction_date,
	SUM(CASE WHEN amount % 2 = 0 THEN 0 ELSE amount END) AS odd_sum,
	SUM(CASE WHEN amount % 2 = 0 THEN amount ELSE 0 END) AS even_sum
FROM transactions
GROUP BY transaction_date
ORDER BY transaction_date ASC;
SQL
복사