Pivot Quarterly Sales SQL Query | SQL Interview Question

Problem statement

Prompt

Given the sales table below (one row per (year, quarter) of revenue), write a query that pivots it so each year is one row with one column per quarter: year, q1_revenue, q2_revenue, q3_revenue, q4_revenue.

A year with no sales in a quarter shows NULL in that quarter's column.
Order the result by year.
sales.revenue may be NULL; NULL values contribute nothing.

Example

year	quarter	revenue
2023	1	100
2023	2	50
2024	1	75
2024	4	25

Expected result:

year	q1_revenue	q2_revenue	q3_revenue	q4_revenue
2023	100	50	NULL	NULL
2024	75	NULL	NULL	25

Constraints

sales has one row per (year, quarter) — quarters are 1..4.
sales.revenue is nullable.

Tips

Conditional aggregation is the portable pivot: SUM(CASE WHEN quarter = 1 THEN revenue END) AS q1_revenue, repeated per quarter, grouped by year.
SUM ignores NULL, so a quarter with no matching rows yields NULL.

Schema

CREATE TABLE sales (
    year    INTEGER NOT NULL,
    quarter INTEGER NOT NULL CHECK (quarter BETWEEN 1 AND 4),
    revenue INTEGER  -- NULL allowed
);

Sample data

INSERT INTO sales (year, quarter, revenue) VALUES
  (2023, 1, 100),
  (2023, 2, 50),
  (2023, 3, 75),
  (2023, 4, 25),
  (2024, 1, 90),
  (2024, 2, 40),
  (2024, 3, 60),
  (2024, 4, 30);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: conditional aggregation pivot, one row per year.
SELECT year,
       SUM(CASE WHEN quarter = 1 THEN revenue END) AS q1_revenue,
       SUM(CASE WHEN quarter = 2 THEN revenue END) AS q2_revenue,
       SUM(CASE WHEN quarter = 3 THEN revenue END) AS q3_revenue,
       SUM(CASE WHEN quarter = 4 THEN revenue END) AS q4_revenue
FROM sales
GROUP BY year
ORDER BY year;

Key concepts

CASE WHEN
Conditional SUM
Pivot
NULL handling

Pivot Quarterly Sales