Sessions, Gaps, Islands & Funnels

Rows arrive one at a time; people experience them in stretches. A visit is a run of clicks close together, a streak is a run of days without a gap, an outage is a run of failed checks. None of those is a column in any table — they all have to be derived.

This chapter covers the family of techniques that turn a row-per-event table into a row-per-episode one: gaps and islands, sessionisation, funnels, and cohort retention.

The unifying idea is small. Look at the distance between neighbouring rows, decide where that distance counts as a break, and give everything between two breaks the same group id. Everything else is aggregation.

island

A run of consecutive rows with no break — consecutive days, consecutive seat numbers, back-to-back events.

gap

The break between two islands. Defining what counts as a gap is the modelling decision the whole query rests on.

session

An island in an event stream, where the break is a period of inactivity — the classic threshold is 30 minutes.

funnel

An ordered sequence of steps, counted by how many entities reached each one. Order and time window are both part of the definition.

The problem: given a list of dates per entity, find the unbroken runs. The classic solution is one line long and looks like sleight of hand until you see why it works.

Number the rows in date order within each entity. Then subtract the row number from the date. Within a run, the date advances by one day per row and the row number advances by one per row — so the difference is constant. The first gap breaks the lockstep and a new constant begins.

That constant is your group key. Everything after it is a GROUP BY.

with days as (
  -- one row per courier-day, or three deliveries in a day inflate the run
  select distinct courier, delivered_on
  from deliveries
),
grouped as (
  select
    courier,
    delivered_on,
    date(delivered_on,
         '-' || row_number() over (partition by courier order by delivered_on) || ' days'
    ) as run_key
  from days
)
select courier,
       min(delivered_on) as run_start,
       max(delivered_on) as run_end,
       count(*)          as days_in_run
from grouped
group by courier, run_key
order by courier, run_start;

-- ... same days and grouped CTEs ...
, runs as (
  select courier, count(*) as days_in_run
  from grouped
  group by courier, run_key
)
select courier, max(days_in_run) as longest_streak
from runs
group by courier
order by longest_streak desc, courier;

A session is an island in time. The break is not a missing day — it is a period of inactivity longer than some threshold, and the threshold is a product decision rather than a SQL one.

The shape is always the same three steps. Compute the gap to the previous event with LAG. Flag every event whose gap exceeds the threshold as the start of a session. Take a running sum of those flags to number the sessions.

Thirty minutes is the conventional default because it came from web analytics, not because it is right. For a video player it might be four hours; for a payment terminal, ninety seconds.

with gapped as (
  select
    user_id,
    event_at,
    lag(event_at) over (partition by user_id order by event_at) as prev_at
  from events
),
flagged as (
  select
    user_id,
    event_at,
    case
      when prev_at is null then 1                       -- first event ever: new session
      when event_at > datetime(prev_at, '+30 minutes') then 1
      else 0
    end as is_new_session
  from gapped
),
sessioned as (
  select
    user_id,
    event_at,
    sum(is_new_session) over (partition by user_id order by event_at
                              rows between unbounded preceding and current row) as session_no
  from flagged
)
select user_id, session_no,
       min(event_at) as started_at,
       max(event_at) as ended_at,
       count(*)      as events
from sessioned
group by user_id, session_no
order by user_id, session_no;

A funnel counts how many entities reached each step of an ordered sequence. The counting is easy; the definition is where funnels go wrong.

Three decisions have to be made explicit. Must the steps happen in order, or is reaching step 3 enough? Is there a time window? And does a user who did step 1 twice count once or twice?

The portable form is conditional aggregation over one row per user and step — MIN of the timestamp for each step, then comparisons between them.

with first_touch as (
  select
    user_id,
    min(case when step = 'view'     then event_at end) as viewed_at,
    min(case when step = 'add_cart' then event_at end) as carted_at,
    min(case when step = 'purchase' then event_at end) as bought_at
  from events
  group by user_id
)
select
  count(*)                                                        as viewed,
  count(case when carted_at > viewed_at then 1 end)               as carted,
  count(case when bought_at > carted_at
              and carted_at > viewed_at then 1 end)               as purchased
from first_touch
where viewed_at is not null;

select
  count(*) as viewed,
  count(case when carted_at > viewed_at
              and carted_at <= datetime(viewed_at, '+1 day') then 1 end) as carted_24h,
  count(case when bought_at > carted_at
              and bought_at <= datetime(viewed_at, '+1 day') then 1 end) as purchased_24h
from first_touch
where viewed_at is not null;

Retention groups users by when they started and asks how many came back N periods later. The grid is easy to draw and easy to compute wrong.

Two decisions dominate. Is day 7 exactly day 7, or any time within the first seven days? And do you exclude cohorts too young to have had a chance?

The second is the one that produces the classic broken chart: a cohort that signed up three days ago cannot have day-7 retention, and including it as a zero drags the whole curve down.

with cohorts as (
  select user_id, date(min(event_at)) as cohort_day
  from events
  group by user_id
),
activity as (
  select
    c.cohort_day,
    c.user_id,
    cast(julianday(date(e.event_at)) - julianday(c.cohort_day) as integer) as day_offset
  from cohorts as c
  join events as e on e.user_id = c.user_id
)
select
  cohort_day,
  count(distinct case when day_offset = 0 then user_id end) as day_0,
  count(distinct case when day_offset = 1 then user_id end) as day_1,
  count(distinct case when day_offset = 7 then user_id end) as day_7
from activity
where cohort_day <= date('2026-03-01', '-7 days')   -- only cohorts old enough for day 7
group by cohort_day
order by cohort_day;

Four problems, all on datasets this chapter has used. Two build islands from the `deliveries` table; two sessionise and sequence events on the streaming spine.

Say the break definition out loud before you write each one — the SQL is short and the definition is the work.