Gaps and Islands: Finding Streaks: SQL lesson

"What's each user's longest login streak?" is a famous hard interview question. The technique is called gaps and islands: each unbroken run of days is an island.

This lesson uses user_activity from the E-commerce database: one row per user per login day.

The trick: number each user's logins with ROW_NUMBER, then subtract that number from the date. Consecutive days all land on the same value, so it works as a streak ID:

WITH numbered AS (
    SELECT user_id, activity_date,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY activity_date) AS rn
    FROM user_activity
)
SELECT user_id, activity_date, rn,
       date(activity_date,
            '-' || rn || ' days') AS streak_id
FROM numbered;

User 1 logs in from Jan 1 to Jan 5, skips Jan 6, then returns Jan 7 and 8. The first five days share one streak_id and the last two share another.

Now group by that ID to measure each island:

WITH numbered AS (
    SELECT user_id, activity_date,
           ROW_NUMBER() OVER (
               PARTITION BY user_id
               ORDER BY activity_date) AS rn
    FROM user_activity
),
islands AS (
    SELECT user_id, activity_date,
           date(activity_date,
                '-' || rn || ' days') AS streak_id
    FROM numbered
)
SELECT user_id,
       MIN(activity_date) AS streak_start,
       MAX(activity_date) AS streak_end,
       COUNT(*) AS days
FROM islands
GROUP BY user_id, streak_id
ORDER BY user_id, streak_start;

The same pattern solves wins in a row, consecutive active days and unbroken subscriptions.

Examples run on the employees sample database. Open any of them in the playground to experiment.