✏️ Explanatory Question
Level: Coding Round — A LeetCode-style analytics problem; combines date-based grouping with conditional aggregation for approved vs total.
The Puzzle: Given a transactions table, for each month and each country, report: total number of transactions, total amount, number of approved transactions, and total approved amount. This is the bread-and-butter of financial reporting.
transactions| id | country | state | amount | trans_date |
|---|---|---|---|---|
| 1 | IN | approved | 1000 | 2026-08-05 |
| 2 | IN | declined | 500 | 2026-08-12 |
| 3 | IN | approved | 2000 | 2026-08-20 |
| 4 | US | approved | 3000 | 2026-09-01 |
| month | country | trans_count | approved_count | trans_total | approved_total |
|---|---|---|---|---|---|
| 2026-08 | IN | 3 | 2 | 3500 | 3000 |
| 2026-09 | US | 1 | 1 | 3000 | 3000 |
Aug/IN: 3 transactions total (3500), of which 2 approved (3000). The declined 500 counts in totals but not in approved.
The two key techniques: (1) Group by month using DATE_FORMAT(trans_date, '%Y-%m') to bucket dates, and (2) conditional aggregation — SUM(state = 'approved') counts approved rows and SUM(CASE WHEN state='approved' THEN amount ELSE 0 END) sums only approved amounts, all in the same query.
SELECT
DATE_FORMAT(trans_date, '%Y-%m') AS month,
country,
COUNT(*) AS trans_count,
SUM(state = 'approved') AS approved_count,
SUM(amount) AS trans_total,
SUM(CASE WHEN state = 'approved' THEN amount ELSE 0 END) AS approved_total
FROM transactions
GROUP BY DATE_FORMAT(trans_date, '%Y-%m'), country
ORDER BY month, country; Why SUM(state = 'approved') works: The condition state = 'approved' evaluates to 1 (true) or 0 (false) for each row. Summing those gives the count of approved rows — a concise alternative to COUNT(CASE WHEN ... THEN 1 END). This "sum of a boolean" trick is a hallmark of clean analytics SQL.
-- COUNT ignores NULLs, so CASE returning NULL for non-approved also works
SELECT
DATE_FORMAT(trans_date, '%Y-%m') AS month,
country,
COUNT(*) AS trans_count,
COUNT(CASE WHEN state = 'approved' THEN 1 END) AS approved_count,
SUM(amount) AS trans_total,
SUM(CASE WHEN state = 'approved' THEN amount END) AS approved_total
FROM transactions
GROUP BY month, country; COUNT + CASE nuance: COUNT(CASE WHEN ... THEN 1 END) works because COUNT ignores the NULLs returned when the condition is false (no ELSE needed). But for SUM(amount), an approved-only sum needs the CASE to return NULL or 0 for non-approved — both are ignored/added harmlessly, but be deliberate about which you use.
Interviewer follow-up: "Months with zero transactions are missing from the report — the chart has gaps. Fix it." → Generate a complete month series (recursive CTE or a calendar table) and LEFT JOIN the transactions onto it, using COALESCE(..., 0) for empty months. This is the same gap-filling technique from Q136 — grouping only shows months that have data, so you must supply the full timeline yourself.