Recap — CASE WHEN Pivoting

# CASE WHEN Pivoting Sometimes PMs want a wide-format table: *"For each user, show their first-purchase amount, average purchase, and total purchase, as three columns."* The data is in long format (one row per purchase). You need to pivot. PostgreSQL has `crosstab`. SQL Server has `PIVOT`. BigQuery has `PIVOT()`. MySQL has nothing. The portable answer: `CASE WHEN` inside an aggregate. ## The pattern Long format: | user_id | event | value | |---|---|---| | 1 | signup | 1 | | 1 | first_purchase | 49 | | 1 | total_purchase | 295 | Wide format: | user_id | signups | first_purchase_amt | total_purchase_amt | |---|---|---|---| | 1 | 1 | 49 | 295 | The SQL: ```sql SELECT user_id, SUM(CASE WHEN event = 'signup' THEN value ELSE 0 END) AS signups, SUM(CASE WHEN event = 'first_purchase' THEN value ELSE 0 END) AS first_purchase_amt, SUM(CASE WHEN event = 'total_purchase' THEN value ELSE 0 END) AS total_purchase_amt FROM user_events GROUP BY user_id; ``` The trick: `CASE WHEN` produces a value only for matching rows; SUM/COUNT aggregates them by user. You can pivot any number of columns this way. ## Conditional counts ```sql SELECT DATE_TRUNC('month', event_time) AS month, COUNT(*) FILTER (WHERE event = 'signup') AS signups, COUNT(*) FILTER (WHERE event = 'purchase') AS purchases, COUNT(*) FILTER (WHERE event = 'churn') AS churns FROM events GROUP BY 1; ``` `FILTER` is the modern (SQL standard) alternative to `CASE WHEN` for counts. Cleaner to read, identical behavior. ## Dynamic pivoting The problem: you don't know the values to pivot on at query-write time. *"Pivot products into columns — but there are 200 products."* Pure SQL can't do this — you'd need to write 200 CASE WHEN. Two options: 1. **Generate the query in your BI tool / Python**. Most pivot use-cases are for dashboards anyway; Looker/Tableau pivot client-side. 2. **Use a vendor-specific PIVOT function**. PostgreSQL `crosstab`, SQL Server `PIVOT`, BigQuery `PIVOT()`. Each has slightly different syntax. If an interviewer asks "how would you pivot products into columns", the right answer is "I'd identify the top N products and pivot only those, or do this in the BI layer". Trying to dynamically pivot in raw SQL is almost always the wrong approach. ## Unpivot — the reverse Going from wide to long is more common in production. The portable pattern uses UNION ALL: ```sql SELECT user_id, 'signups' AS metric, signups AS value FROM wide UNION ALL SELECT user_id, 'first_purchase_amt', first_purchase_amt FROM wide UNION ALL SELECT user_id, 'total_purchase_amt', total_purchase_amt FROM wide; ``` Ugly for many columns. PostgreSQL has `UNNEST`; SQL Server has `UNPIVOT`. For DA work, long format is usually friendlier — don't pivot prematurely. ## Common interview wrong answers 1. **Using PIVOT in an MySQL interview** — MySQL has no PIVOT. Use CASE WHEN. 2. **Hardcoding values that should be parameterised** — if products are dynamic, generate the query in code, not in SQL. 3. **Pivoting when the consumer is a BI tool** — let Looker/Tableau pivot. Output long-format from SQL. ## Takeaway `SUM(CASE WHEN ... THEN ... END)` or `COUNT(*) FILTER (WHERE ...)` is the universal pivot. Memorize the shape. PIVOT and crosstab are nice when you have them but never something to assume.