Skip to main content

// advent-of-postgres/day-23

Day 23 of 24AdvancedSessionizationLAG

The Tracking Sessions

Children refresh the gift tracker in bursts. Turn a stream of page views into sessions with a 30-minute gap rule.

Opens 23 December 2026, 00:00 UTC

The gift tracker is the busiest page at the North Pole on 23 December. Shinny wants to know how many visits it really gets, not how many page loads, to size the servers for tomorrow.

Every page view is in page_views:

Column Type Notes
id bigint Primary key
child_id int Who was looking
viewed_at timestamptz When the page loaded

The team counts visits as sessions. A child's session starts with their first view. A new session starts when more than 30 minutes have passed since that child's previous view. Exactly 30 minutes is still the same session. Sessions do not stop at midnight or at the top of an hour.

Your task

How many sessions are in the table?

Submit the number.

Stuck? Show a hint

Compare each view with the same child's previous view (LAG, partitioned by child). A gap over 30 minutes, or no previous view at all, starts a session.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.