Daily Page Views Filled from a Spine — Sessionization and Funnels in SQL

The problem

Scenario: Streamhub's analytics team needs a complete day-by-day timeline of 'page_view' activity for January 2022, including days with no recorded events.

Task: Write a query to return each date from January 1, 2022 through January 31, 2022 alongside the count of 'page_view' events recorded on that date.

Assumptions:

The events table holds one row per recorded event, with event_type recording the kind of activity and occurred_at recording when it happened.
A 'page_view' event has event_type equal to 'page_view'.
Some dates in the range have no recorded 'page_view' events; those dates must still appear in the result, with page_view_count reported as a missing value rather than 0.

Output:

One row per date in the range, including dates with no 'page_view' events.
Columns in this order: day, page_view_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
  date_spine AS (
    SELECT
      GENERATE_SERIES('2022-01-01'::date, '2022-01-31'::date, INTERVAL '1 day')::date AS DAY
  ),
  daily_page_views AS (
    SELECT
      occurred_at::date AS event_day,
      COUNT(*) AS event_count
    FROM
      events
    WHERE
      event_type = 'page_view'
    GROUP BY
      occurred_at::date
  )
SELECT
  ds.day,
  dpv.event_count AS page_view_count
FROM
  date_spine ds
  LEFT JOIN daily_page_views dpv ON dpv.event_day = ds.day
ORDER BY
  ds.day

The shape

generate_series produces a row for every date in the window, and a LEFT JOIN from that spine to a daily count of 'page_view' events keeps the quiet days in the result with NULL on the count side. The two-CTE structure separates "what dates do we want" from "what activity actually happened."

Clause by clause

The date_spine CTE runs generate_series('2022-01-01'::date, '2022-01-31'::date, INTERVAL '1 day')::date AS day. The series generates one timestamp per day across the range; the trailing ::date cast pins each result to a calendar date.
daily_page_views groups the events to one row per day with occurred_at::date AS event_day, COUNT(*) AS event_count FROM events WHERE event_type = 'page_view' GROUP BY occurred_at::date. Filtering before aggregating means COUNT only sees page-view rows.
The outer FROM date_spine ds LEFT JOIN daily_page_views dpv ON dpv.event_day = ds.day keeps every spine day in the result. When the right side has no match, dpv.event_count comes back as NULL, which is the missing-value the prompt asks for.
SELECT ds.day, dpv.event_count AS page_view_count and ORDER BY ds.day produce the requested two-column shape and sort.

Why this and not an `INNER JOIN` with `COUNT` from `events`

If you started from events and grouped by day, January 1 through January 14 would disappear from the result because no rows exist for those dates. There would be nothing to group. The spine flips the direction of the read: every date is in the driving table, and the events table contributes only where it matches. That is what guarantees all 31 rows.

The trap

A COALESCE(dpv.event_count, 0) is the obvious-looking fix for the empty days, and on most "no activity" reports it would be the right call. The prompt explicitly asks for a missing value rather than 0, which is a distinction the grader checks: 0 and NULL are not the same value, and substituting one for the other changes 30 of the 31 result rows here.

You practiced left-joining a date spine to per-day event counts — preserving the missing-value count on quiet days, in contrast to substituting 0.

Return each date from January 1, 2022 through January 31, 2022 alongside the count of `'page_view'` events recorded on that date

The shape

Clause by clause

Why this and not an `INNER JOIN` with `COUNT` from `events`

The trap

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

The shape

Clause by clause

Why this and not an INNER JOIN with COUNT from events

The trap

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

Why this and not an `INNER JOIN` with `COUNT` from `events`