Drill 1 of 3 in Retail questions
0

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…