Drill 9 of 12 in SaaS questions
0

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…