Pivot quarterly sales with conditional aggregation
Return each region with sales totals q1 through q4. Quarter is 1–4, region and amount are non-NULL, and absent quarters have total zero. Return only the requested columns and follow the specified ordering. This question probes conditional sum is portable unlike vendor-specific pivot syntax, and else 0 fills absent quarters.
Input
Setup script defining sales(id INT, region VARCHAR(20), quarter INT, amount INT).
Output
region, q1, q2, q3, q4; ascending region.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE sales(id INT, region VARCHAR(20), quarter INT, amount INT); INSERT INTO sales VALUES (1,'East',1,100),(2,'East',1,50),(3,'East',3,200),(4,'West',2,80);
Expected result
region | q1 | q2 | q3 | q4 East | 150 | 0 | 200 | 0 West | 0 | 80 | 0 | 0
Another dataset (1)
CREATE TABLE sales(id INT, region VARCHAR(20), quarter INT, amount INT); INSERT INTO sales VALUES (1,'Only',4,0),(2,'Only',4,30);
Expected result
region | q1 | q2 | q3 | q4 Only | 0 | 0 | 0 | 30
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Group by region.
- Use one SUM(CASE...) per quarter.
Target complexity: time O(n) · space O(n)