For each merchantcategory, return two columns in one row: total spend in March 2026 (marspend) and in April 2026…
SQLLevel: FoundationCASE WHEN
For each merchant_category, return two columns in one row: total spend in March 2026 (mar_spend) and in April 2026 (apr_spend).
Table definitions (from the source exercise):
CREATE TABLE transactions(transaction_id INTEGER PRIMARY KEY, account_id INTEGER, transaction_date TEXT, amount REAL, merchant_category TEXT);
Hints
- Write an interleaving of two concurrent operations.
- Which observations does the isolation level permit, and what happens on a mid-operation failure?
Answer
Approach: Two SUM(CASE WHEN MONTH(...) = x THEN amount ELSE 0 END) columns - a manual pivot.
SELECT merchant_category,
SUM(CASE WHEN MONTH(transaction_date) = 3 THEN amount ELSE 0 END) AS mar_spend,
SUM(CASE WHEN MONTH(transaction_date) = 4 THEN amount ELSE 0 END) AS apr_spend
FROM transactions
WHERE YEAR(transaction_date) = 2026
GROUP BY merchant_category;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT