Daily Event Counts Zero Filled — Date Spines in SQL

The problem

Scenario: Streamhub's engagement team is reviewing platform activity for the first week of March 2024 and needs a complete day-by-day view, with quiet days called out explicitly.

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.

Assumptions:

The events table holds one row per recorded event, with the timestamp stored in occurred_at.
Some dates in the range have no recorded events; those dates must still appear in the result with a count of zero.

Output:

One row per date in the range, including dates with no events.
Columns in this order: day, event_count.
Sorted by day ascending.

Schema · analytics 5 tables

users

id integer

name text

email text

country text

plan text

signed_up_at timestamptz

is_active boolean

conversions

id integer

user_id integer

converted_at timestamptz

plan text

amount numeric

sessions

id integer

user_id integer

started_at timestamptz

ended_at? timestamptz

event_count integer

events

id integer

user_id integer

session_id? integer

event_type text

occurred_at timestamptz

properties? jsonb

periods

id integer

name text

start_month integer

end_month integer

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

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 event_count
FROM
  spine s
  LEFT JOIN events e ON e.occurred_at::date = s.day
GROUP BY
  s.day
ORDER BY
  s.day

The shape

Quiet days in the first week of March have to show up explicitly with a count of zero, so the spine generates every date in the range and events is left-joined onto it. The seven rows come from the spine; the count comes from whatever happens to match.

Clause by clause

WITH spine AS (SELECT generate_series('2024-03-01'::date, '2024-03-07'::date, '1 day'::interval)::date AS day) builds a seven-row backbone — one row per calendar day from March 1 through March 7. The outer ::date cast strips off the timestamp portion so the join key is a plain date.
SELECT s.day, COUNT(e.id) AS event_count returns the spine's date and the count of matched events. COUNT(e.id) skips nulls, so an unmatched spine row contributes zero rather than one.
FROM spine s LEFT JOIN events e ON e.occurred_at::date = s.day attaches each event to its day. The LEFT JOIN keeps every spine row even when no events match; that's what surfaces March 2 through March 7 with event_count = 0.
GROUP BY s.day collapses the joined rows back to one row per spine date. Grouping on the spine column is what guarantees one output row per generated date.
ORDER BY s.day returns the seven dates in calendar order.

The trap

Casting occurred_at to a date inside the ON clause is the load-bearing detail. occurred_at is a timestamp, so an event recorded at 2024-03-01 14:32:00 does not equal the spine's 2024-03-01 plain date. Without the cast, every event misses the join and every day reports zero — silently, with no error.

You practiced anchoring the result to a generated date spine with a left-join so days with no activity still appear in the output with a count of zero.

Return each date from March 1, 2024 through March 7, 2024 alongside the number of `events` recorded on that date

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.