Sessionize events
SQL
Hard
3 views
Problem Description
In LoginLogs(user_id, logged_at), start a new session if gap > 30 minutes. Assign session_number per user.
Sample Test Case
Input:
LoginLogs(user_id, logged_at)
Output:
Rows with session_number
Constraints
Use LAG + SUM over gaps
Official Solution
SELECT user_id, logged_at, SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY logged_at) AS session_number FROM (SELECT user_id, logged_at, CASE WHEN LAG(logged_at) OVER (PARTITION BY user_id ORDER BY logged_at) IS NULL THEN 1 WHEN logged_at > LAG(logged_at) OVER (PARTITION BY user_id ORDER BY logged_at) + INTERVAL '30' MINUTE THEN 1 ELSE 0 END AS is_new_session FROM LoginLogs) t;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!