Deviopath › PostgreSQL

Split Page Views into Sessions

Hard · Window Functions

Sessionization groups a user's events into visits. Two steps with window functions: first flag each row that starts a new session (compare with the previous row using LAG), then turn the flags into session numbers with a running SUM.

A user's page views, in order of viewed_at, belong to the same session as long as each view comes at most 30 minutes after the previous one. A gap of more than 30 minutes starts a new session (exactly 30 minutes is still the same session). The first view of a user always starts session 1.

Return one row per session: user_id, session_no (1, 2, ... per user, in time order), started_at (time of the first view), views (number of views) and duration (last view time minus first view time, an interval like 00:40:00). Sort by user_id, then session_no.

Next: Bill of Materials Cost