How do you approach SQL: Pivot via Conditional Aggregation?

Conditional aggregation: SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1.

What are common mistakes on SQL: Pivot via Conditional Aggregation?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Pivot via Conditional Aggregation

HardDatabases

Description

This problem is paper-only - see explanation for the canonical SQL solution.

Given Sales(region, quarter, amount), pivot the data into one row per region with one column per quarter (q1, q2, q3, q4). Use 0 for missing buckets. Sort by region ascending.

For the auto-grader: return [{ region, q1, q2, q3, q4 }]. quarter is one of "Q1"|"Q2"|"Q3"|"Q4".

Example 1

Input: sales = [{region:"NA",quarter:"Q1",amount:100},{region:"NA",quarter:"Q2",amount:200},{region:"EU",quarter:"Q1",amount:50}]

Output: [{region:"EU",q1:50,q2:0,q3:0,q4:0},{region:"NA",q1:100,q2:200,q3:0,q4:0}]

Constraints

- 0 <= sales.length <= 10^4

SQL: Pivot via Conditional Aggregation: approach, complexity and pitfalls

A hard sql problem, graded against 7 test cases (5 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

If you are stuck

Show the 2-step hint ladder

Conditional aggregation: SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1.
Postgres has a `crosstab()` extension and SQL Server has PIVOT, but the case-when-sum form is portable.

The optimal approach

Show the approach: Canonical SQL (portable):

```sql

SELECT region,

SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS q1,

SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS q2,

SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS q3,

SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS q4

FROM Sales

GROUP BY region

ORDER BY region;

```

Engine-specific alternatives:

- SQL Server: `PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2],[Q3],[Q4])) p`

- Postgres: `crosstab` from the `tablefunc` extension.

- Oracle: native `PIVOT` clause.

The conditional-aggregation form works everywhere and reads clearly. Use engine-native pivot only when the column set is dynamic and large.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.