Interview Questions/SQL/User Session Reconstruction from Event Stream

User Session Reconstruction from Event Stream

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

Medium

User Session Reconstruction from Event Stream

You are given a UserEvents table of timestamped events. A new session begins whenever the gap between two consecutive events for the same user exceeds 30 minutes (1800 seconds). The very first event for each user always starts session 1. For each session, compute: user_id, session_num (1-based per user), session_start (earliest event_time), session_end (latest event_time), event_count, and duration_seconds (session_end minus session_start in seconds). Return results ordered by user_id, then session_num. Table: UserEvents

Column Name	Type	Description
event_id	INT	Primary key
user_id	INT	User identifier
event_time	DATETIME	Timestamp of event (ISO format)

Examples

Example 1

Use LAG to compare each event to the previous one per user. Mark a new session when the gap > 1800s or there is no previous event. Use SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) as a session counter. Aggregate per user+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