Streamhub's product team monitors daily event volume across the platform.
Write a query to return every date from '2024-01-01' through '2024-01-07' alongside the number of events recorded that day. Days with no events should appear with a count of 0.
Assumptions:
- The
eventstable has one row per event with anidand anoccurred_attimestamp. - The seven dates form a contiguous calendar sequence; every date in the range must appear in the result.
- For each date, the event count is the number of events whose
occurred_at::dateequals that date. Dates with no events show a count of0.
Output:
- One row per date in the seven-day range, with columns
dayandevent_count.
Schema · analytics 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
WITH
spine AS (
SELECT
GENERATE_SERIES('2024-01-01'::date, '2024-01-07'::date, INTERVAL '1 day')::date AS DAY
)
SELECT
s.day,
COUNT(e.id) AS event_count
FROM
spine s
LEFT JOIN events e ON e.occurred_at::date = s.day
GROUP BY
s.dayThe shape
The spine CTE generates the seven-day window, and the LEFT JOIN against events keeps every generated day in the result whether or not events matched. COUNT(e.id) ignores the NULL produced for unmatched days, so quiet days surface with a count of 0 without extra handling.
Clause by clause
WITH spine AS (SELECT generate_series('2024-01-01'::date, '2024-01-07'::date, interval '1 day')::date AS day)builds the date spine. Each generated timestamp is cast back todateso the join againste.occurred_at::datelines up on type.SELECT s.day, COUNT(e.id) AS event_countreturns each date and its event count.COUNT(e.id)counts non-NULL values only; unmatched spine rows have NULL ine.idand contribute0to the count.FROM spine s LEFT JOIN events e ON e.occurred_at::date = s.daypairs each generated day with any events recorded that day. TheLEFT JOINkeeps every spine row, including the ones with no matching event.GROUP BY s.daycollapses multiple events per day into a single row per date.
Why this and not a query directly against events
SELECT occurred_at::date AS day, COUNT(*) FROM events GROUP BY day looks like the same report, but it only produces rows for days that actually have events. Days with zero events disappear entirely. The spine is what guarantees every date in the window appears, even the empty ones. Without it, the product dashboard would silently skip quiet days.
You practiced the same zero-fill spine pattern over an event-stream table — same shape as the orders version, applied to a different schema's fact table.
