Pivot Rows into Columns in SQL | Conditional Aggregation | SQL Interview Prep #14

Code & Test Automation |    / @codetestautomation   Learn how to pivot rows into monthly columns in SQL with conditional aggregation! In this lesson we cover the problem (completed revenue for January, February and March as separate columns for every customer, including zero-spend customers), the customers, orders and order_items tables, the intuition of routing each line amount into one month column with CASE, the query clause by clause, a dry run of the routing, the real result in SQLite and PostgreSQL, the traps of putting the status filter in WHERE (which drops customers), of forgetting ELSE 0, and of guessing the last day of a month instead of half-open ranges, the follow-up of an unknown number of months, and index notes. Episode 14 of 20 in SQL Interview Prep (filtering, NULLs, grouping, joins, anti-joins, EXISTS, window functions, pivots, relational division, gaps and islands, recursive CTEs and cohort retention). Every query is run on SQLite 3.54.1 and PostgreSQL 17.11 against the guide's setup data. CHAPTERS 0:00 Introduction 0:50 The problem 1:29 The tables 2:02 Intuition 2:39 The query 3:32 Dry run 4:19 Run it 4:48 The traps 6:41 Follow-up 7:25 Index notes 7:53 Recap 8:23 Subscribe THE QUERY (SQL) SELECT c.customer_name, SUM(CASE WHEN o.order_date ›= '2026-01-01' AND o.order_date ‹ '2026-02-01' THEN i.quantity * i.unit_price ELSE 0 END) AS jan, SUM(CASE WHEN o.order_date ›= '2026-02-01' AND o.order_date ‹ '2026-03-01' THEN i.quantity * i.unit_price ELSE 0 END) AS feb, SUM(CASE WHEN o.order_date ›= '2026-03-01' AND o.order_date ‹ '2026-04-01' THEN i.quantity * i.unit_price ELSE 0 END) AS mar FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'Completed' LEFT JOIN order_items i ON i.order_id = o.order_id GROUP BY c.customer_id, c.customer_name ORDER BY c.customer_id; RESULT (verified on SQLite 3.54.1 and PostgreSQL 17.11, matches the guide) customer_name | jan | feb | mar Arjun | 5000 | 3000 | 7000 Beena | 2000 | 4000 | 1000 Carlos | 0 | 0 | 1500 Divya | 1500 | 0 | 4000 Esha | 0 | 0 | 0 Faisal | 0 | 0 | 0 Practice tip: run the setup script, type the query yourself, then explain it out loud: the grain, the NULLs, the ties and the ordering. Next episode: #15 Calculate percentage of total. #SQL #SQLInterview #DataEngineering #CodeAndTestAutomation Subscribe to Code & Test Automation for more SQL and coding interview prep:    / @codetestautomation