Interview Question Bank · Conditional SUM · FILTER · growth %
1. Region sales, 2024 next to 2023
EasyRetailQuestion #276
Concept: Conditional SUM · FILTER · growth %show
To put two periods side by side, aggregate once and split the total with conditional sums: SUM(x) FILTER (WHERE year = 2023) and SUM(x) FILTER (WHERE year = 2024), or SUM(CASE WHEN ... THEN x END). Growth is (new - old) / old, always divided by the earlier base period. Group by the level you report on, not the level the data is stored at.
Example (different data):
SELECT warehouse, SUM(units) FILTER (WHERE shipped_on < DATE '2025-01-01') AS units_2024, SUM(units) FILTER (WHERE shipped_on >= DATE '2025-01-01') AS units_2025 FROM shipments GROUP BY warehouse;
Your task
Hearthline Stores, a big-box chain, records quarterly sales for each store. For the annual review, the regional VPs want each region's 2024 sales shown next to its 2023 sales.
Return region, sales_2023, sales_2024 and growth_pct (100 * (sales_2024 - sales_2023) / sales_2023, rounded to 1 decimal), one row per region. Sort by growth_pct descending, then region.
Starting Postgres in your browser…
Checking your access…