Interview Drills · Pivot with FILTER / CASE
29. Spend by category, one row per account
Concept: Pivot with FILTER / CASEshow
To pivot rows into columns, group by the row key and compute one aggregate per column, each restricted to one value: SUM(x) FILTER (WHERE category = 'a') in Postgres, or the portable SUM(CASE WHEN category = 'a' THEN x END).
Example (different data):
SELECT store, COUNT(*) FILTER (WHERE status = 'open') AS open, COUNT(*) FILTER (WHERE status = 'closed') AS closed FROM tickets GROUP BY store;
Your task
Account managers want a one-line summary per customer account showing spend in each category side by side.
Return account_id, groceries, travel and dining (total spend in each category) with one row per account, sorted by account_id.
Starting Postgres in your browser…
Checking your access…