Interview Question Bank · generate_series · LEFT JOIN · zero-filled weeks
8. Weekly active learners, empty weeks included
MediumEducationQuestion #224
Concept: generate_series · LEFT JOIN · zero-filled weeksshow
GROUP BY only returns buckets that have data, so a week with no activity simply disappears. Build the full list of weeks with generate_series(start, end, INTERVAL '1 week'), LEFT JOIN the activity onto it, and count a column from the joined table, which gives 0 for empty weeks. Match events with ts >= week_start AND ts < week_start + INTERVAL '1 week' so late-Sunday timestamps stay in their week.
Example (different data):
SELECT d.day::date AS day, COUNT(o.id) AS orders FROM generate_series(TIMESTAMP '2025-05-01', TIMESTAMP '2025-05-07', INTERVAL '1 day') AS d(day) LEFT JOIN bakery_orders o ON o.ordered_at >= d.day AND o.ordered_at < d.day + INTERVAL '1 day' GROUP BY d.day ORDER BY d.day;
Your task
Brightpath Academy's growth team charts weekly active learners for its self-paced courses: anyone who started at least one lesson that week. March 2025 included a spring break, and the chart must still show that week.
For the four weeks starting Monday 2025-03-03, 2025-03-10, 2025-03-17 and 2025-03-24, return week_start (as a date) and active_learners (distinct students who started a lesson from that Monday through Sunday, 0 if none). Sort by week_start.
Starting Postgres in your browser…
Checking your access…