Drill 7 of 12 in E-commerce questions
0

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…