Interview Question Bank · Pivot with SUM FILTER · COALESCE · quarter boundaries
7. Category revenue by quarter
MediumE-commerceQuestion #91
Concept: Pivot with SUM FILTER · COALESCE · quarter boundariesshow
To pivot long data into columns, GROUP BY the row label and write one aggregate per column, each restricted to its own slice: SUM(x) FILTER (WHERE ...) in Postgres, or SUM(CASE WHEN ... THEN x ELSE 0 END) anywhere. A FILTER that matches no rows returns NULL, not 0, so wrap it in COALESCE when the report should show zero.
Example (different data):
SELECT store, COALESCE(SUM(amount) FILTER (WHERE sold_on < '2025-07-01'), 0) AS h1, COALESCE(SUM(amount) FILTER (WHERE sold_on >= '2025-07-01'), 0) AS h2 FROM sales GROUP BY store;
Your task
Quillmere's finance lead wants a compact table for the board: one row per category, one column per quarter.
Using completed orders (order lines without a product are ignored), return category, q3_2024, q4_2024 and q1_2025: the category's revenue (quantity * unit_price) in July-September 2024, October-December 2024 and January-March 2025. Show 0, not NULL, for a quarter with no sales. One row per category with at least one completed sale, sorted by category.
Starting Postgres in your browser…
Checking your access…