Average Session Time per User

Problem Given web event logs (user_id, event_time, page/url), write SQL to compute the average session time per user, where a session is a burst of activity separated from the next by a gap (e.g. > 30 minutes of inactivity starts a new session).

Input / Output

• Input: events(user_id, event_time) table.
• Output: (user_id, avg_session_duration).

Constraints

• Sessionization must be done in SQL — expect window functions, not application code.

Example

• User A with events at 10:00, 10:10, 11:00 (gap 50 min) → two sessions: 10 min and 0 min → average 5 min.

Expected approach

• Three-step CTE pipeline: (1) LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) to compute gaps; (2) flag new_session = gap > interval '30 minutes', then a running SUM of flags as session_id; (3) GROUP BY user_id, session_id for session duration (max − min), then AVG per user. Discuss the single-event-session (duration 0) convention and choosing the gap threshold.