// advent-of-postgres/day-23
The Tracking Sessions
Children refresh the gift tracker in bursts. Turn a stream of page views into sessions with a 30-minute gap rule.
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.
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.
We earn commissions when you shop through the links below.
Bitrise
Managed runners for GitHub Actions
Bitrise Build Hub runs your GitHub Actions jobs on M4 Macs and AMD Linux machines. Change runs-on and keep your workflows, secrets and logs on GitHub.
Atomsized
AWS platform engineering and GitOps
Design and automation for reliable AWS and Kubernetes platforms, safer delivery workflows, and preview and UAT environments your engineers can understand and own.
DigitalOcean
Cloud infrastructure for developers
Simple, reliable cloud computing designed for developers
DevDojo
Developer community & tools
Join a community of developers sharing knowledge and tools
SMTPfast
Developer-first email API
Send transactional and marketing email through a clean REST API. Detailed logs, webhooks, and embeddable signup forms in one dashboard.
QuizAPI
Developer-first quiz platform
Build, generate, and embed quizzes with a powerful REST API. AI-powered question generation and live multiplayer.
Want to support DevOps Daily and reach thousands of developers?
Become a Sponsor