SQL gaps and islands for consecutive activity days

The gaps-and-islands pattern groups adjacent observations into continuous runs. For daily activity, an island is a sequence of consecutive active dates for the same customer; a gap begins when the next active date is more than one day later.

The pattern is useful for attendance streaks, consecutive service failures or repeated daily usage. Its interpretation depends on the calendar and data completeness. A missing row might mean inactivity, a nonworking day or a failed ingestion process.

The advanced SQL lab includes a small daily_activity table. A is active on January 1, 2, 3, 5 and 6. B is active on January 2, 4 and 5. In this exercise, each customer/date pair is unique and absent dates mean no activity.

Mark where a new island starts

First compare each date with the previous date for that customer. Then mark the first row or a gap greater than one day as a new island:

WITH ordered AS (
    SELECT customer_id, active_date,
        LAG(active_date) OVER (
            PARTITION BY customer_id ORDER BY active_date
        ) AS previous_date
    FROM daily_activity
), starts AS (
    SELECT customer_id, active_date,
        CASE
            WHEN previous_date IS NULL THEN 1
            WHEN julianday(active_date) - julianday(previous_date) > 1
                THEN 1
            ELSE 0
        END AS starts_island
    FROM ordered
), numbered AS (
    SELECT customer_id, active_date,
        SUM(starts_island) OVER (
            PARTITION BY customer_id ORDER BY active_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS island_number
    FROM starts
)
SELECT customer_id, island_number,
       MIN(active_date) AS start_date,
       MAX(active_date) AS end_date,
       COUNT(*) AS active_days
FROM numbered
GROUP BY customer_id, island_number
ORDER BY customer_id, start_date;

Expected islands:

The cumulative sum assigns the same island number until another start flag appears. The numbers are local to each customer and this source snapshot; they are not permanent business identifiers.

Validate the input grain

If your source has one row per event, several events on the same date can inflate the final COUNT. Reduce the source to distinct customer/date pairs before applying the pattern, or aggregate to the intended daily eligibility rule.

Do not simply drop duplicate events without understanding them. The daily transformation is a deliberate change of grain: “at least one qualifying event that day.” A separate event-count analysis may still need every original event.

Also reject or report invalid dates. If date parsing yields NULL, a comparison can become unknown and fail to start a new island when it should. Missing timestamps deserve an exception policy, not accidental placement inside a streak.

SQLite's window and date-function references explain the operations used here. Window functions, date functions.

Calendar days are not always the right adjacency

A working-day attendance streak may continue from Friday to Monday. Our query would treat that as a gap because it uses calendar-day distance.

For business-day continuity, use a calendar table with a sequence number for eligible working days. Two active dates are adjacent when those sequence numbers differ by one. Public holidays and company-specific schedules need to be represented in that calendar.

Similarly, a “consecutive weekly” measure needs an explicit week definition. ISO weeks, Sunday-start weeks and rolling seven-day windows are different calendars. Do not reuse a daily formula by changing only the label.

Distinguish a closed streak from an ongoing one

If A's last observed date is January 6 and the source cutoff is January 6, you do not yet know whether the streak ends there. If the source is complete through January 8 and A has no later activity, the gap is observed.

Include the observation cutoff when reporting current streaks. A late-arriving January 4 record would join A's two islands into a single six-day run. That means historical streak labels can change when source completeness changes.

If the metric drives an operational action, decide whether to wait for a completeness watermark or allow restatements. A quick dashboard refresh should not quietly rewrite consequential decisions without a reviewable policy.

Test the pattern by changing one date

Add January 4 for A in an isolated copy. The expected result becomes one January 1–6 island with six active days. Remove January 2 instead, and the initial three-day island splits.

These mutations test the actual adjacency rule rather than only checking a memorized output. Also verify that the sum of island lengths equals the number of eligible customer/date pairs.

Exercise: adapt the pattern to runs of consecutive failed daily checks. State whether an unexecuted check counts as failure, success or unknown. The SQL should follow that decision rather than silently equating absence with failure.

NeuraPath's Data Analytics with Generative AI programme includes advanced SQL and pipeline verification. Gaps-and-islands work is a useful way to practise defining continuity before interpreting a streak as evidence of behaviour.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Retention in SQL: distinguish active users from returning users.
• Continue with Sessionize event data with a clear inactivity rule.