SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…