SQL gaps, islands, and streaks: finding consecutive activity and sessions

Finding structure in a sequence

Many analytical questions are about more than the value of one event. They are about the relationship between events in a sequence: on how many consecutive days was a customer active, when did activity stop for several days, which events belong to the same session, what is each customer’s longest streak, which periods contain missing data. Consider the following activity events:

For Asha, the active dates are July 1, 2, 3, then July 6, 7. There is a break between July 3 and July 6, so we can think of Asha’s activity as two continuous periods: July 1 to July 3, and July 6 to July 7. These continuous periods are commonly called islands. The breaks between them are gaps. The number of consecutive periods inside an island can form a streak. The same idea can create user sessions when a break is based on inactivity time rather than calendar dates.

Event Event Event     Event Event
  |     |     |         |     |
  +-----+-----+         +-----+
      Island      ^        Island
                  |
                 Gap

The main question is: what condition causes one island to end and another to begin? For daily activity, the rule might be “start a new island when the next active date is more than one day after the previous.” For sessions, “start a new session when more than 30 minutes have passed.” Once the break rule is clear, gaps and islands become much easier to solve.

First, define the correct grain

Our source table contains individual events, and Asha has two events on July 1 (09:00 and 09:12). But if the requirement is calculate consecutive active days, we do not want one row per event; we want one row per customer + calendar day. So first normalize the data:

WITH activity_days AS (
    SELECT DISTINCT
        customer,
        CAST(event_at AS DATE) AS activity_date
    FROM activity_events
)
SELECT *
FROM activity_days
ORDER BY
    customer,
    activity_date;

This is now the correct input grain for daily streak calculations.

Finding the previous active date with LAG

To detect gaps, compare each active date with the customer’s previous active date:

SELECT
    customer,
    activity_date,
    LAG(activity_date) OVER (
        PARTITION BY customer
        ORDER BY activity_date
    ) AS previous_activity_date
FROM activity_days
ORDER BY
    customer,
    activity_date;

For Asha:

Now the break becomes visible: July 3 -> July 6. Three calendar days separate the observed dates, and July 4 and July 5 contain no activity.

Finding missing periods

Suppose the requirement is show gaps of at least one missing calendar day between customer activity. We can calculate the missing boundaries:

WITH activity_days AS (
    SELECT DISTINCT
        customer,
        CAST(event_at AS DATE) AS activity_date
    FROM activity_events
),
with_previous AS (
    SELECT
        customer,
        activity_date,
        LAG(activity_date) OVER (
            PARTITION BY customer
            ORDER BY activity_date
        ) AS previous_activity_date
    FROM activity_days
)
SELECT
    customer,
    previous_activity_date + 1 AS gap_start,
    activity_date - 1 AS gap_end
FROM with_previous
WHERE activity_date > previous_activity_date + 1
ORDER BY
    customer,
    gap_start;

For Asha, previous activity is July 3 and the next is July 6, so July 4 and July 5 are missing. The gap boundaries are gap_start = previous date + 1 day and gap_end = next date - 1 day.

Grouping consecutive dates together

Now suppose the requirement is group each customer’s consecutive active days into streaks. For Asha, July 1, 2, 3 belong together, and July 6, 7 belong to another group. We first need to identify where each new streak begins.

Step 1: mark the breaks

Compare each date with the previous one and flag where a new island starts:

SELECT
    customer,
    activity_date,
    previous_activity_date,
    CASE
        WHEN previous_activity_date IS NULL THEN 1
        WHEN activity_date > previous_activity_date + 1 THEN 1
        ELSE 0
    END AS new_streak
FROM with_previous;

For Asha:

A value of 1 means a new island begins here; 0 means continue the current island.

Step 2: turn breaks into an island ID

Now use a cumulative sum over new_streak:

SELECT
    customer,
    activity_date,
    SUM(new_streak) OVER (
        PARTITION BY customer
        ORDER BY activity_date
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS streak_id
FROM marked;

For Asha, the cumulative sum produces 1, 1, 1, 2, 2. Those numbers identify the islands:

Step 3: summarize each streak

Now group by customer + streak_id and calculate the start date, end date, and number of active days:

WITH activity_days AS (
    SELECT DISTINCT
        customer,
        CAST(event_at AS DATE) AS activity_date
    FROM activity_events
),
with_previous AS (
    SELECT
        customer,
        activity_date,
        LAG(activity_date) OVER (
            PARTITION BY customer
            ORDER BY activity_date
        ) AS previous_activity_date
    FROM activity_days
),
marked AS (
    SELECT
        customer,
        activity_date,
        CASE
            WHEN previous_activity_date IS NULL THEN 1
            WHEN activity_date > previous_activity_date + 1 THEN 1
            ELSE 0
        END AS new_streak
    FROM with_previous
),
islands AS (
    SELECT
        customer,
        activity_date,
        SUM(new_streak) OVER (
            PARTITION BY customer
            ORDER BY activity_date
            ROWS BETWEEN UNBOUNDED PRECEDING
                     AND CURRENT ROW
        ) AS streak_id
    FROM marked
)
SELECT
    customer,
    MIN(activity_date) AS streak_start,
    MAX(activity_date) AS streak_end,
    COUNT(*) AS streak_days
FROM islands
GROUP BY
    customer,
    streak_id
ORDER BY
    customer,
    streak_start;

Now we have converted individual activity dates into continuous activity periods.

Multiple events on one day inflate the streak

Return to the raw events for Asha: July 1 at 09:00, July 1 at 09:12, July 2 at 10:00, July 3 at 11:00. Suppose someone skips the SELECT DISTINCT customer, CAST(event_at AS DATE) step and calculates the streak directly from events, eventually using COUNT(*) for the first island.

-- Asha, raw events:
-- July 1 09:00  -> event 1
-- July 1 09:12  -> event 2
-- July 2 10:00  -> event 3
-- July 3 11:00  -> event 4
 
-- A: 3    B: 4    C: 2    D: the events cannot form a streak

The fix: normalize before sequencing

For daily streaks, start with SELECT DISTINCT customer, CAST(event_at AS DATE) AS activity_date so that one row equals one active customer-day, before applying LAG, break detection, cumulative grouping, and COUNT.

Finding the longest streak

Once streaks are summarized, finding the longest one is a ranking problem. Rank streaks within each customer and keep rn = 1:

WITH streaks AS (
    -- previous streak-building query
    SELECT
        customer,
        streak_id,
        MIN(activity_date) AS streak_start,
        MAX(activity_date) AS streak_end,
        COUNT(*) AS streak_days
    FROM islands
    GROUP BY
        customer,
        streak_id
),
ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer
            ORDER BY
                streak_days DESC,
                streak_end DESC
        ) AS rn
    FROM streaks
)
SELECT
    customer,
    streak_start,
    streak_end,
    streak_days
FROM ranked
WHERE rn = 1;

The ranking rule says: longest streak first, and if streak lengths tie, prefer the more recent one. Again, the tie-breaker should follow the business requirement.

Sessions are another kind of island

A session is also an island; the difference is the break rule. For daily streaks, a new island starts when more than one calendar day separates activity dates. For sessions, a new island starts when inactivity exceeds a chosen threshold. Suppose a product defines: events belong to the same session when no more than 30 minutes pass between consecutive events. For Asha, 09:00 to 09:12 is a 12-minute gap, so those two July 1 events belong to the same session; the next gap is much larger than 30 minutes, so a new session begins.

Step 1: find the previous event

Lag the timestamp itself within each customer:

SELECT
    event_id,
    customer,
    event_at,
    LAG(event_at) OVER (
        PARTITION BY customer
        ORDER BY event_at, event_id
    ) AS previous_event_at
FROM activity_events;

Step 2: mark new sessions

Using a 30-minute threshold, flag a new session when the gap exceeds the interval:

SELECT
    *,
    CASE
        WHEN previous_event_at IS NULL THEN 1
        WHEN event_at - previous_event_at > INTERVAL '30 minutes' THEN 1
        ELSE 0
    END AS new_session
FROM with_previous;

For Asha’s first two events, 09:00 starts a new session and 09:12 stays in the same session, because 12 minutes <= 30 minutes.

Step 3: create session IDs

The same cumulative-sum technique used for streaks now works for sessions:

SELECT
    *,
    SUM(new_session) OVER (
        PARTITION BY customer
        ORDER BY event_at, event_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS session_id
FROM marked;

For Asha, the sessions become 09:00 -> 1, 09:12 -> 1, July 2 -> 2, July 3 -> 3, July 6 -> 4, July 7 -> 5. For Ben, July 4 12:00 and July 4 12:20 share session 2 because those events are only 20 minutes apart.

Summarizing sessions

Once events have session IDs, ordinary aggregation calculates session statistics. A session has become a normal group, so MIN(event_at), MAX(event_at), COUNT(*), and session duration all follow from a GROUP BY customer, session_id.

Missing periods need an expected calendar

LAG can detect gaps between observed dates. But suppose the reporting period is July 1 through July 31 and a customer has activity only on July 10 and July 11. LAG can detect gaps between those observed rows, but it cannot by itself tell you that July 1 to July 9 or July 12 to July 31 are also missing, because no observed row exists before or after those boundaries. To identify every missing date in an expected reporting period, you generally need an explicit expected calendar, sometimes called a calendar table, date spine, or date dimension, then compare expected dates against observed dates using an anti-join or NOT EXISTS.

What does consecutive mean?

Before writing any gaps-and-islands query, define what “consecutive” means. Are Friday and Monday consecutive? If the metric means consecutive calendar days, no (Saturday and Sunday are missing); if it means consecutive business days, perhaps yes. Similarly, January and March may represent a gap if monthly data is expected, but not if the business records activity only quarterly. The SQL needs an expected cadence, whether that is consecutive calendar days, business days, events, months, events separated by less than 30 minutes, or records with sequential version numbers. There is no universal definition of a gap.

Another common islands pattern

For consecutive dates, you may also encounter a pattern based on ROW_NUMBER(). Assign row numbers to the dates (July 1 -> 1, July 2 -> 2, July 3 -> 3, July 6 -> 4, July 7 -> 5), then conceptually subtract the row-number offset from each date. Inside consecutive sequences, the resulting anchor remains constant, and that constant value can be used as an island identifier. It is a compact technique, but the break flag plus cumulative sum approach above is often easier to extend, because the break rule can become anything: gap greater than 1 day, gap greater than 30 minutes, status changed, version skipped, or value crossed a threshold. For learning and production readability, explicitly defining the break condition is often a strong starting point.

How to spot gaps-and-islands problems

Think about gaps and islands when the requirement says consecutive active days, longest streak, missing dates, continuous activity period, break in activity, sessionize events, inactivity longer than 30 minutes, consecutive months, continuous status period, or find periods between observed events.

Watch for these common mistakes:

• Event rows are counted when the metric is active days.
• PARTITION BY is missing and sequences cross customers.
• ORDER BY does not fully define event order.
• The break threshold uses > when the requirement means >=, or vice versa.
• Calendar days are assumed when the business means business days.
• LAG is expected to find missing dates outside the observed range.
• Sessions are grouped by calendar date instead of inactivity.
• A streak query counts duplicate activity within the same period.

The same four-step pattern

Order the rows
      v
Compare with previous row
      v
Mark the breaks
      v
Cumulatively count breaks
      v
Group rows with the same island ID

This mental model works for a surprisingly large number of analytical SQL problems.

One more prediction

Suppose Asha is active on July 1, 2, 3, 6, 7. The business defines consecutive activity as activity on adjacent calendar days. How many islands are there?

Active dates:
July 1
July 2
July 3
July 6
July 7
 
Rule: consecutive = activity on adjacent calendar days
 
-- A: 1    B: 2    C: 3    D: 5

Summary

Gaps, islands, streaks, and sessions are all variations of the same analytical problem: where does one continuous sequence end and another begin? Start by defining the sequence with LAG(activity_date) OVER (PARTITION BY customer ORDER BY activity_date). Then define a break with a CASE that returns 1 when previous_activity_date IS NULL or activity_date > previous_activity_date + 1, otherwise 0. Turn those breaks into island IDs with a cumulative SUM(...) OVER (... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), then summarize each island with GROUP BY customer, streak_id. For sessions the structure is the same; only the break rule changes to event_at - previous_event_at > INTERVAL '30 minutes'.