When the Output Looks Like a Pivot
Concepts covered: Conditional Aggregation, Grouping
The question never says pivot. It says 'a column for active customers and a column for inactive ones per region', or 'revenue split into premium and standard', or 'signups and converters per cohort'. Each phrasing describes an output that is wider than the input: the table has one row per customer and a single status column, and the answer has one row per region and a column for every status value the asker cares about. Wider output from a long table is the signal.
- Per region, I need separate columns for active and inactive customers
- Break revenue down by tier, premium and standard side by side
- Show signups and the number who converted, per cohort month
- Turn this long table of status rows into a wide summary
- How many orders were over 100 dollars, and how many in total, per store
Long in, wide out
A WHERE clause keeps or drops whole rows, so a query with WHERE status = 'active' can produce the active count and nothing else: the inactive rows left the query before GROUP BY ever saw them. The output the interviewer described needs the active count and the inactive count in the same row, which means both sets of rows have to reach the group, and each column has to decide for itself which of the grouped rows it counts. So the condition goes inside the aggregate and is checked against each row.
Read the answer table column by column and the query follows from it. The column that labels each row, region, is the GROUP BY column. Every other column is an aggregate over that region's rows. A column named after a value in the data is an aggregate that counts only the rows carrying that value. active is one; premium and converted work the same way. The way to say 'only these rows' inside an aggregate is a CASE that returns something for a matching row and NULL or 0 for the rest. The total column is the same aggregate with no condition, and North's 0 under inactive is a region with no matching rows still getting its row.
| As asked | Group by | Categories come from | Aggregate |
|---|---|---|---|
| Active and inactive customers per region | region | status | COUNT |
| Revenue by tier, per region | region | tier | SUM(amount) |
| Signups and converters per cohort month | cohort_month | completed_onboarding | COUNT |
| Orders over 100 dollars and total orders per store | store_id | amount > 100 | COUNT |
| Purchases, refunds and net per day | order_date | order_type | SUM(amount) |
The request also arrives from the other direction. The interviewer pastes a 3-row sample of the dashboard tile they want filled and asks for the query behind it. The same reading applies: find the column whose values are labels, treat it as the group, and treat every numeric column as an aggregate whose name tells you its condition. A tile headed premium_revenue is SUM over amount where tier is premium; one headed orders with no qualifier is COUNT(*).
The sentence to say before writing
State the plan in one sentence before typing: 'I'll group by region and use conditional aggregates, a CASE inside each COUNT or SUM, to get one column per status.' That sentence shows the interviewer you recognized the shape and know the technique by name, and it commits you to a single query, so the next 60 seconds are typing rather than deciding. A candidate who opens with SELECT region, COUNT(*) FROM customers WHERE status = 'active' is answering a third of the question and will throw the query away when asked where the other columns are.
- 3 filtered queries
- SELECT region, COUNT(*) ... WHERE status = 'active', then the same again for inactive and churned
- 3 result sets that someone then stitches together in application code, if not by hand
- A change to the grouping has to be made in 3 places
- Reads to the interviewer as not having seen the shape
- One grouped query
- One GROUP BY region over the whole table
- Each status column is an aggregate with its own CASE condition
- Every region appears once, with 0 in a column where it has no rows
- On the screen within a minute, with the total beside the slices
About this lesson
This section is part of the Conditional Aggregation: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.