SQL · medium
Pivot a long table of (user, metric, value) into one row per user.
Asked in BI Developer interviews, in the SQL round.
Short answer
Use conditional aggregation: MAX(CASE WHEN metric='x' THEN value END).
Sample data
These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.
metrics
| user_id | metric | value |
|---|---|---|
| 1 | visits | 14 |
| 1 | purchases | 2 |
| 1 | revenue | 120 |
| 2 | visits | 6 |
| 2 | purchases | 1 |
| 2 | revenue | 45 |
| 3 | visits | 22 |
| 3 | revenue | 300 |
How to answer it
Definition
Turn rows into columns. Long format has one row per user and metric; wide format has one row per user with a column per metric. In portable SQL this is conditional aggregation.
Syntax
SELECT key_column,
AGGREGATE(CASE WHEN label = 'x' THEN value END) AS x,
AGGREGATE(CASE WHEN label = 'y' THEN value END) AS y
FROM long_table
GROUP BY key_column;Example
SELECT user_id,
MAX(CASE WHEN metric = 'sessions' THEN value END) AS sessions,
MAX(CASE WHEN metric = 'revenue' THEN value END) AS revenue,
MAX(CASE WHEN metric = 'refunds' THEN value END) AS refunds
FROM user_metrics
GROUP BY user_id;Output
user_id | sessions | revenue | refunds
--------+----------+---------+---------
101 | 12 | 240.00 | 0.00
204 | 3 | 19.00 | NULLWhy the CASE has no ELSE
With no ELSE, a non-matching row yields NULL, and every aggregate here ignores NULLs. Writing ELSE 0 would turn a missing metric into a real zero, which is a different claim: "we measured zero" rather than "we never measured it".
MAX or SUM
MAX assumes at most one row per user and metric, and silently keeps the largest if that assumption is wrong. SUM assumes they should be added. Neither is safer; they encode different beliefs about the grain, so pick the one that matches and say why.
Key points
- The column list must be written out. SQL cannot invent columns from data at runtime.
- No
ELSEmeans missing stays NULL, which is usually the honest answer. - A dynamic set of metrics needs generated SQL or a pivot in the BI tool. Say that rather than pretending SQL can do it.
- Some engines have
PIVOT, but it is the same conditional aggregation with different spelling.
FAQ
- How do I go the other way? Unpivot, either with
UNION ALLper column or an engine'sUNPIVOT. - What if a metric name has an apostrophe or a space? Quote the alias, or better, map metric names to safe aliases upstream.
- Does this scale to a hundred metrics? Not comfortably. That is a signal the long format should stay long until the BI layer.
The 30-second answer
Conditional aggregation: group by the user, and for each metric take a MAX of a CASE that returns the value only when the metric matches, otherwise NULL. One CASE per output column.
The point worth making is that the column list has to be hard coded, because SQL fixes its output columns before it sees the data. If the set of metrics is dynamic that is generated SQL or a pivot in the BI tool, not something a static query can do. I would also leave the ELSE off deliberately, so a metric nobody recorded comes back NULL rather than zero. Zero says we measured nothing happening; NULL says we did not measure.
In the interview
- What they are checking: that you know the
CASEinside an aggregate pattern and can say why the aggregate is there. - Common mistake: forgetting the aggregate and the
GROUP BY, which gives one row per input row with mostly NULLs.