Interview Questions/SQL/Identify User Sessions from Page View Events

Identify User Sessions from Page View Events

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Identify User Sessions from Page View Events

You are given a PageViews table. A new session begins when the gap between two consecutive page views for the same user exceeds 30 minutes (1800 seconds). Write a SQL query to identify all sessions per user. For each session return: user_id, a sequential session_id (starting at 1 per user), the session_start timestamp, the session_end timestamp, and the page_views count. Return the result ordered by user_id, then session_id. Table: PageViews

Column Name	Type	Description
id	INT	Primary key
user_id	INT	ID of the user
viewed_at	DATETIME	Timestamp of the page view

Examples

Example 1

User 1: gap between 10:25 and 11:10 is 45 min > 30 min → new session. User 2: gap between 09:00 and 09:35 is 35 min > 30 min → new session.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.

query.sql