SQL interview question: pivot monthly category revenue
Problem
Shared data rulesFor each category, find revenue from orders placed in July, August and Sep 1–27, 2026, one column per month. A month with no sales is 0.
Formula
revenue = Σ quantity × price_usdover the category’s line items
Example
orders| order_id | created_at | status | Row note |
|---|---|---|---|
| 101 | 2026-07-15 13:00 | delivered | |
| 102 | 2026-08-12 18:30 | delivered | |
| 103 | 2026-09-10 15:00 | delivered | |
| 104 | 2026-09-28 03:00 | delivered | ← Sep 28 — outside the report |
- Order 104: Sep 28 — outside the report
order_items| order_id | category | quantity | price_usd |
|---|---|---|---|
| 101 | Dairy | 2 | 2.40 |
| 102 | Produce | 3 | 1.80 |
| 102 | Dairy | 1 | 3.00 |
| 103 | Produce | 2 | 4.00 |
| 104 | Dairy | 1 | 10.00 |
| category | revenue_jul | revenue_aug | revenue_sep |
|---|---|---|---|
| Dairy | 4.8 | 3 | 0 |
| Produce | 0 | 5.4 | 8 |
Order 102 has two categories: Produce 3 × $1.80 = $5.40 and Dairy $3.00. Dairy has no sales in Sep 1–27, so its September cell is 0.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.
Skills it tests
More interview questions
- SQL: Category pairs bought togetherMedium · Pairs and self-joins
- SQL: Recursive employee hierarchyMedium · Hierarchies and recursion
- SQL: Customers who bought every categoryMedium · Relational division
- SQL: A/B test conversion rate by platformMedium · A/B tests
- SQL: 30-day customer lifetime valueHard · Product metrics