Interview Question Bank · generate_series · LEFT JOIN · half-open ranges
9. Weekly active workspaces, including empty weeks
HardSaaSQuestion #117
Concept: generate_series · LEFT JOIN · half-open rangesshow
Grouping an events table can only produce periods that have events, so a quiet week silently disappears from the chart. Generate the full calendar with generate_series, LEFT JOIN the events onto it, and count a column from the events side so empty periods show 0. Match a timestamp to its week with a half-open range: >= week_start AND < week_start + INTERVAL '1 week'.
Example (different data):
SELECT d.day::date, COUNT(o.id) AS orders FROM generate_series(TIMESTAMP '2025-01-01', TIMESTAMP '2025-01-07', INTERVAL '1 day') AS d(day) LEFT JOIN orders o ON o.placed_at >= d.day AND o.placed_at < d.day + INTERVAL '1 day' GROUP BY d.day ORDER BY d.day;
Your task
Tallyboard had an outage-prone stretch in March 2025, and the exec dashboard needs weekly active workspaces with every week shown, even ones where nobody did anything.
For the four weeks starting Monday 2025-03-03, 2025-03-10, 2025-03-17 and 2025-03-24, return week_start (date) and active_workspaces (distinct accounts with any event from that Monday 00:00 up to the next Monday 00:00), with 0 for weeks without events. Sort by week_start.
Starting Postgres in your browser…
Checking your access…