Sessions out of raw events in ab-test-checkout

Sessions out of raw events in ab-test-checkout comes up often enough to be worth a note. ab-test-checkout is a convenient thing to try it on — 6,000 rows, one row per exposed user.

with gapped as (
  select *,
         exposed_on - lag(exposed_on) over (partition by user_id order by exposed_on) as since_prev
  from ab_test_checkout
)
select *,
       sum(case when since_prev is null
                  or since_prev > interval '30 minutes'
                then 1 else 0 end)
         over (partition by user_id order by exposed_on) as session_no
from gapped;

Thirty minutes is a choice, not a constant. It should come from the distribution of inter-event gaps in your own data, and it should be written down somewhere a stakeholder can find it, because every session-based metric moves when it changes.

Run it against the real file in the SQL playground — the engine there is enough of a SQL implementation to execute this as written, and the dataset is already loaded. The pattern page has the version with the failure modes spelled out.

Window functions are the difference between SQL that describes rows and SQL that describes sequences. Almost every "we exported it to pandas to do this bit" turns out to be one of these five.