Quarterly Sales as Columns
Problem
A table sales(region, quarter, amount) holds one row per sale, with quarter a number from 1 to 4. Write a query that turns the quarters into columns: one row per region with columns region, q1, q2, q3 and q4, each holding that region's total for the quarter, or 0 when the region sold nothing in it. Sort by region.
Examples
Input: sales = [("east", 1, 100), ("east", 2, 50), ("west", 1, 70), ("east", 1, 30), ("west", 4, 20)]
Output: [("east", 130, 50, 0, 0), ("west", 70, 0, 0, 20)]Input: sales = [("north", 3, 5), ("north", 3, 5), ("north", 3, 5)]
Output: [("north", 0, 0, 15, 0)]
Why: every sale falls in quarter 3, the other three columns still appear as 0Input: sales = []
Output: []
Why: edge case, no sales means no regions and no rowsHints
0 / 3
GROUP BY region gives the one row per region you want. The hard part is four different totals in that one row, each of which should only see some of the rows.
WHERE cannot help, because it removes a row from every column at once. You need a filter that applies to one column only.
Inside the SUM, write CASE WHEN quarter = 1 THEN amount ELSE 0 END, and repeat it for quarters 2, 3 and 4. The ELSE 0 matters: without it a quarter with no sales sums to NULL.
Solution
This is a pivot by conditional aggregation. GROUP BY region collapses each region into one row, and each output column is a SUM over a CASE expression that passes a row's amount through only when it belongs to that column's quarter and contributes 0 otherwise. The CASE is a per-column filter: WHERE quarter = 1 would throw the other quarters' rows away for every column, while the CASE lets each column keep its own rows. The ELSE 0 is deliberate, because SUM over nothing but NULLs is NULL, not 0. Some databases have a PIVOT keyword or FILTER (WHERE …) clause, but CASE inside an aggregate works everywhere. The query reads the table once and groups it, O(n) with hashing.
import sqlite3
QUERY = """
SELECT region,
SUM(CASE WHEN quarter = 1 THEN amount ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 2 THEN amount ELSE 0 END) AS q2,
SUM(CASE WHEN quarter = 3 THEN amount ELSE 0 END) AS q3,
SUM(CASE WHEN quarter = 4 THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY region
ORDER BY region
"""
def run(rows):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE sales (region TEXT, quarter INTEGER, amount INTEGER)")
db.executemany("INSERT INTO sales VALUES (?, ?, ?)", rows)
return db.execute(QUERY).fetchall()
print(run([("east", 1, 100), ("east", 2, 50), ("west", 1, 70), ("east", 1, 30), ("west", 4, 20)])) # -> [('east', 130, 50, 0, 0), ('west', 70, 0, 0, 20)]
print(run([("north", 3, 5), ("north", 3, 5), ("north", 3, 5)])) # -> [('north', 0, 0, 15, 0)]
print(run([])) # -> []Stuck on the idea rather than the code? WHERE and Filtering covers it.