Recap — 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:

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

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:

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.