Interview Question Bank · Month × group grid · LAG · NULLIF · RANK NULLS LAST
12. Ranking departments by month-over-month growth
HardHealthcareQuestion #144
Concept: Month × group grid · LAG · NULLIF · RANK NULLS LASTshow
Before using LAG for month-over-month change, build every month for every group (CROSS JOIN groups with generate_series, LEFT JOIN the facts), so a month with zero activity is a 0 row and LAG doesn't jump over the gap. Growth from 0 is undefined: NULLIF(prev, 0) makes it NULL instead of an error. When ranking with DESC, Postgres puts NULLs first, so say NULLS LAST.
Example (different data):
WITH g AS ( SELECT s.store, m.month, COUNT(o.id) AS orders FROM (SELECT DISTINCT store FROM orders) s CROSS JOIN generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-04-01', INTERVAL '1 month') AS m(month) LEFT JOIN orders o ON o.store = s.store AND o.placed_on >= m.month AND o.placed_on < m.month + INTERVAL '1 month' GROUP BY s.store, m.month ) SELECT store, month, orders, ROUND(100.0 * (orders - LAG(orders) OVER w) / NULLIF(LAG(orders) OVER w, 0), 1) AS growth FROM g WINDOW w AS (PARTITION BY store ORDER BY month);
Your task
Larkspur Health's operations review ranks outpatient departments by how fast their visit volume grew each month. Departments are compared across both clinics combined.
For March, April and May 2025, return month (YYYY-MM), department, visits, prev_visits (the previous calendar month's visits, 0 if none), growth_pct (100 * (visits - prev_visits) / prev_visits rounded to 1 decimal, NULL when prev_visits is 0) and growth_rank (RANK within the month, fastest growth first, NULL growth last). Sort by month, growth_rank, then department.
Starting Postgres in your browser…
Checking your access…