Compute per-coin quarterly amounts and totals

Problem (SQL: Conditional Aggregation)

You have two tables:

coins

• coin_id INT PRIMARY KEY
• name VARCHAR

transactions

• id BIGINT PRIMARY KEY
• coin_id INT NOT NULL
  • Foreign key referencing coins.coin_id
• dt DATETIME (assume UTC)
• amount DECIMAL(18,2)

Each row in transactions is a single transaction for a coin.

Task

For a given calendar year (use a parameter :year), output one row per coin with:

• name
• q1_amount: sum of amount for transactions in Q1 (Jan–Mar) of :year
• q2_amount: sum of amount for Q2 (Apr–Jun) of :year
• q3_amount: sum of amount for Q3 (Jul–Sep) of :year
• q4_amount: sum of amount for Q4 (Oct–Dec) of :year
• total_transactions: total number of transactions for that coin in :year
• total_amount: total sum of amount for that coin in :year

Notes / Requirements

• Include coins with no transactions in :year (their amounts/counts should be 0).
• Output columns exactly as listed above.

Per-coin quarterly transaction amounts with totals (conditional aggregation)

You are given two tables: coins(coin_id, name) and transactions(id, coin_id, dt, amount).

Write a SQL query that returns one row per coin with the following columns:

• name
• q1_amount: total transaction amount in Q1 (Jan–Mar)
• q2_amount: total transaction amount in Q2 (Apr–Jun)
• q3_amount: total transaction amount in Q3 (Jul–Sep)
• q4_amount: total transaction amount in Q4 (Oct–Dec)
• total_transactions: total number of transactions for the coin
• total_amount: total transaction amount for the coin

If a coin has no transactions in a quarter, return 0 for that quarter. Use calendar quarters based on transactions.dt (assume all sample data is within the same year).

Table coins

column	type	constraints
coin_id	INT	PRIMARY KEY, NOT NULL
name	VARCHAR(50)	NOT NULL

Sample data:

coin_id	name
1	Bitcoin
2	Ethereum
3	Dogecoin

Table transactions

column	type	constraints
id	INT	PRIMARY KEY, NOT NULL
coin_id	INT	NOT NULL
dt	DATE	NOT NULL
amount	DECIMAL(12,2)	NOT NULL

Sample data:

amount	coin_id	dt	id
100	1	2025-01-10	1
50	1	2025-03-05	2
25	1	2025-04-01	3
200	1	2025-11-20	4
80	2	2025-02-14	5
120	2	2025-06-30	6
60	2	2025-07-01	7
7.5	3	2025-05-10	8
10	3	2025-08-15	9
5	3	2025-12-31	10
75	1	2025-09-09	11

Expected output

One row per coin showing quarterly summed amounts (Q1–Q4), plus total transaction count and total amount. Quarters with no transactions should show 0.

name	q1_amount	q2_amount	q3_amount	q4_amount	total_amount	total_transactions
Bitcoin	150	25	75	200	450	5
Dogecoin	0	7.5	10	5	22.5	3
Ethereum	80	120	60	0	260	3

Hints

1. Use conditional aggregation: SUM(CASE WHEN ... THEN amount END) for each quarter.
2. LEFT JOIN from coins to transactions so coins with no transactions still appear (use COALESCE to return 0).