Scenario: Streamhub's engagement team needs both daily and cumulative event counts across the first week of March 2024 to chart momentum on the platform.
Task: Write a query to return each date from March 1, 2024 through March 7, 2024 alongside the number of events recorded on that date and the total number of events from March 1, 2024 through that date inclusive.
Assumptions:
- The
eventstable holds one row per recorded event, with the timestamp stored inoccurred_at. - Some dates in the range have no recorded
events; those dates must still appear in the result with a daily count of zero. - The cumulative value on each date covers every event recorded from March 1, 2024 through that date inclusive.
Output:
- One row per date in the range, including dates with no
events. - Columns in this order:
day,daily_events,cumulative_events. - Sorted by
dayascending.
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-03-01'::date, '2024-03-07'::date, '1 day'::INTERVAL)::date AS DAY
)
SELECT
s.day,
COUNT(e.id) AS daily_events,
SUM(COUNT(e.id)) OVER (
ORDER BY
s.day
) AS cumulative_events
FROM
spine s
LEFT JOIN events e ON e.occurred_at::date = s.day
GROUP BY
s.day
ORDER BY
s.dayThe shape
The chart needs both the daily count and a running total advancing across quiet days, so the spine produces the seven rows and an aggregate window function runs the cumulative sum on top of the zero-filled count in a single pass.
Clause by clause
WITH spine AS (SELECT generate_series('2024-03-01'::date, '2024-03-07'::date, '1 day'::interval)::date AS day)builds the seven-row backbone for the first week of March.COUNT(e.id) AS daily_eventsis the per-day aggregate. BecauseCOUNT(e.id)ignores nulls, days with no matching event report zero — March 2 through 7.SUM(COUNT(e.id)) OVER (ORDER BY s.day) AS cumulative_eventswraps the daily count in a windowed sum. The innerCOUNTcollapses each spine day to its daily count; the outerSUM ... OVER (ORDER BY s.day)accumulates those per-day counts in date order. The default frame for an ordered aggregate window includes every row from the start through the current row — exactly the running-total shape.FROM spine s LEFT JOIN events e ON e.occurred_at::date = s.dayattaches each event to its day. TheLEFT JOINkeeps every spine row, including March 2 through 7.GROUP BY s.daycollapses the joined rows back to one row per spine date.ORDER BY s.dayreturns the seven dates in calendar order.
Why this and not a self-join cumulative
A self-joining approach — joining the spine to itself or to a daily-counts query on day <= current_day — produces the same numbers but re-reads the data once for each output row. The window-function form visits each row exactly once. On a weekly query the cost is invisible; on a yearly daily dashboard the difference is noticeable.
The trap
The cumulative is correct only because the daily count for quiet days is zero, not missing. If the join were an INNER JOIN instead of a LEFT JOIN, March 2 through 7 would not appear at all — the cumulative line would skip from 2 on March 1 straight to whatever the next match was, instead of holding at 2 across the quiet stretch. The zero-fill is what makes the running total honest.
You practiced layering a running total on top of zero-filled daily counts so the cumulative line advances continuously across quiet days.
