Streaks and sessions: gaps and islands

Two shapes of the same problem

“How long is this user’s current daily-login streak?” and “split this stream of clicks into visits” look unrelated. Both are the same operation: walk the rows in order, decide where one run ends and the next begins, and number the runs. SQL people call the runs islands and the breaks gaps, and there are two tricks, one for each shape.

Islands of consecutive values

A user logged in on 3, 4, 5, 9, 10 and 14 March. That is three streaks. Number the rows and subtract:

SELECT user_id, login_day,
       login_day - (row_number() OVER (PARTITION BY user_id ORDER BY login_day))::int AS grp
FROM logins;

For the six days the row numbers are 1 to 6, and login_day - row_number gives 2 March, 2 March, 2 March, 5 March, 5 March, 8 March. Within a streak, the date and the row number advance together, so their difference stays constant. At a gap the date jumps and the row number does not, so the difference changes. That constant is an arbitrary label for the streak, and grouping by it finishes the job:

WITH g AS (... the query above ...)
SELECT user_id, min(login_day) AS streak_start, max(login_day) AS streak_end,
       count(*) AS days
FROM g
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

The longest streak per user is a max(days) over that; the current streak is the island whose streak_end is today. The trick works on any evenly spaced sequence: consecutive invoice numbers, consecutive weeks, consecutive months if you convert months to a running integer first. It needs each value to appear once per partition, so deduplicate a login table with several logins a day before applying it.

Sessions defined by a gap in time

Clicks are not evenly spaced, so the row-number trick does not apply. Instead, decide the gap that separates two sessions, flag each row that starts a new one, and take a running count of the flags:

WITH flagged AS (
  SELECT user_id, clicked_at,
         CASE WHEN clicked_at - lag(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at)
                   > interval '30 minutes'
              OR lag(clicked_at) OVER (PARTITION BY user_id ORDER BY clicked_at) IS NULL
              THEN 1 ELSE 0 END AS new_session
  FROM clicks
),
numbered AS (
  SELECT *, sum(new_session) OVER (PARTITION BY user_id ORDER BY clicked_at
                                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
  FROM flagged
)
SELECT user_id, session_no,
       min(clicked_at) AS started, max(clicked_at) AS ended,
       max(clicked_at) - min(clicked_at) AS duration,
       count(*) AS clicks
FROM numbered
GROUP BY user_id, session_no;

Three layers: compare each row with its predecessor and flag a break; running-sum the flags so that every row carries its session number; group by that number. The first row of each user has no predecessor and is flagged as a session start. Every layer uses something from earlier in this module, and this is the query where they all meet.

The threshold is a definition, not a fact

Thirty minutes of inactivity is the convention web analytics tools inherited from the 1990s. It is not a property of the data. Change it to 10 minutes and a user who reads a long article becomes two sessions; change it to two hours and an afternoon of intermittent browsing becomes one. The number of sessions per user, the average session length and every “conversion per session” rate move with the threshold, sometimes by a factor of two. Write the threshold into the query as a named constant, put it in the report’s caption, and be suspicious of any session count compared across two systems that may not share it.

Where the islands trick pays for itself

• Machine uptime from a status log: islands of status = 'up' give outage windows and their lengths.
• Membership history: islands of consecutive paid months give each tenure, and the gaps are churn-and-return events.
• Data quality: islands in a sequence that should be unbroken (invoice numbers, sensor readings every minute) surface the exact ranges that are missing.

Each of these is a min, max and count per island, once the island has a label.

The habit

Draw six rows on paper before writing either query. Mark where the runs break. Then decide whether the break is “the next value is not adjacent” (row-number trick) or “the next value is too far away” (lag-and-flag trick). The wrong choice produces a query that runs and returns something plausible, which is the recurring theme of this course.