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).

How to answer it

Conditional aggregation. One CASE per output column, wrapped in an aggregate so the group collapses to a single row.

SELECT
  user_id,
  MAX(CASE WHEN metric = 'sessions' THEN value END) AS sessions,
  MAX(CASE WHEN metric = 'orders'   THEN value END) AS orders,
  MAX(CASE WHEN metric = 'revenue'  THEN value END) AS revenue
FROM user_metrics
GROUP BY user_id;

MAX is the usual choice because each (user, metric) pair is expected to appear once; if a metric can appear several times, say whether SUM or the latest value is correct, because the aggregate is the business rule. This works in every database. Postgres has crosstab and SQL Server has PIVOT, but the column list is still fixed at write time; a pivot with an unknown set of metrics needs dynamic SQL or is done in the application.

• What they are checking: that you know the CASE inside 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.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Find the top 3 products by revenue within each category.
• Compute month-over-month user retention from an events table.

Practice this for real

• Check your resume against a BI Developer job description — free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews — two free, no card