Skip to content
Quantoros

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

Related questions