Drill 8 of 12 in Education questions
0

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…