Session Metrics Rollup — Solution & ExplanationProblems / Session Metrics Rollup / EditorialApproach

Flag session starts. LAG(event_time) per user (ordered by time); NULL previous → first session, gap > 1800 seconds → new session. Comparing in seconds sidesteps any minute-truncation fuzziness at the 30-minute boundary.

Number sessions with a running SUM. The prefix sum of 0/1 flags is the classic flag→group-id transform: session_seq stays constant within a session and increments at each flagged row. The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame makes the running (not total) sum unmistakable.

Roll up. GROUP BY user_id, session_seq: MIN is session_start, COUNT(*) the events, and MAX - MIN (seconds / 60.0, rounded) the duration — single-event sessions produce 0.00 with no special-casing.

Key InsightSessionization is the canonical flag-then-prefix-sum pattern: a *local* judgment (this row starts something new, via LAG) is converted into a *global* grouping key by a running sum. Once you see that two-step shape, dozens of "segment the sequence" problems — streaks, shifts, outage windows — reduce to the same skeleton, and everything after it is ordinary GROUP BY arithmetic.Hints

Sessionize in two window passes: LAG finds each event's gap from the previous one (first event of a user has NULL — that's a session start too), producing a 0/1 new-session flag.

A running SUM of the flags — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, per user, ordered by time — turns flags into session numbers: every 1 increments, every 0 inherits.

The rollup is then an ordinary GROUP BY (user_id, session_seq): MIN(event_time) is the start, MAX minus MIN is the duration. Single-event sessions get MAX = MIN, so 0.00 falls out naturally.

Mind the boundary: the rule is gap STRICTLY greater than 30 minutes; the data has a session with an exactly-30-minute internal gap that must stay whole. Compare in seconds (> 1800) to avoid truncation surprises.

Solve Session Metrics Rollup yourself →