SQL interview question: sessionization from event gaps

HardTime intervals

Problem

Rebuild user sessions from events of Sep 7–27, 2026. A gap of over 30 minutes between a user’s events starts a new session. For each platform and weekday of the session’s first event, count sessions and find average duration and events per session.

Formula

duration = last event − first event, in minutes with fractions (one event — 0)

Example

Input: events

event_id	user_id	event_ts	event_name	platform	Row note
1	1	2026-09-14 10:00	session_start	android
2	1	2026-09-14 10:10	add_to_cart	android
3	1	2026-09-14 10:10	add_to_cart	android	← duplicate of 2 — one event
4	1	2026-09-14 10:40	session_start	android	← 30 min later — same session
5	1	2026-09-14 11:25	add_to_cart	android	← 45 min later — new session
6	3	2026-09-06 23:50	session_start	ios	← Sep 6 — outside the period
7	3	2026-09-07 00:10	add_to_cart	ios	← Sep 7 — Monday

Event 3: duplicate of 2 — one event
Event 4: 30 min later — same session
Event 5: 45 min later — new session
Event 6: Sep 6 — outside the period
Event 7: Sep 7 — Monday

Output

platform	weekday	sessions	avg_minutes	avg_events
android	1	2	20	2
ios	1	1	0	1

User 1 has two Monday sessions: events 1–4 (40 min, 3 events) and event 5 (0 min, 1 event), 20 min and 2 events on average. Event 6 is outside the period, so user 3’s session is event 7 alone.

Output columns

weekday: weekday of the session start: 1 = Monday … 7 = Sunday

Notes

Rows with the same user_id, event_ts and event_name are one event.

Solve it free

Your query runs on PostgreSQL in your browser. No sign-up needed.

Skills it tests

Per-user event order