SQL Gaps and Islands: Consecutive Days, Streaks and Missing Values

"Find users who logged in on 3 consecutive days" and "find missing invoice numbers" are the same family of problem: gaps and islands. An island is a run of consecutive values; a gap is a missing stretch between them. One trick, subtracting a row number from each value, solves most of them.

What Are Gaps and Islands?

The Classic Island Pattern — ROW_NUMBER Trick

-- Sample: user login dates (may contain duplicates)
-- login_date: 2025-01-01, 01-02, 01-03, 01-05, 01-06, 01-09

-- STEP 1: Deduplicate and assign row numbers
WITH deduped AS (
    SELECT DISTINCT user_id, login_date FROM logins
),
-- STEP 2: Create grouping key by subtracting row_number from date
grouped AS (
    SELECT user_id, login_date,
           DATE_SUB(login_date,
               INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY
           ) AS grp
    FROM deduped
)
-- STEP 3: Group by user + grp to get each island
SELECT user_id,
       MIN(login_date) AS streak_start,
       MAX(login_date) AS streak_end,
       DATEDIFF(MAX(login_date), MIN(login_date)) + 1 AS streak_length
FROM grouped
GROUP BY user_id, grp
ORDER BY user_id, streak_start;

Visual: How the ROW_NUMBER trick works

Finding GAPS in a Sequence

-- Find missing invoice IDs in a sequence
-- Method 1: Self-join approach
SELECT t1.invoice_id + 1 AS missing_from,
       MIN(t2.invoice_id) - 1 AS missing_to
FROM invoices t1
JOIN invoices t2 ON t2.invoice_id > t1.invoice_id
WHERE NOT EXISTS (
    SELECT 1 FROM invoices t3
    WHERE t3.invoice_id = t1.invoice_id + 1
)
GROUP BY t1.invoice_id;

-- Method 2: LAG approach (cleaner)
SELECT
    prev_id + 1       AS gap_start,
    invoice_id - 1    AS gap_end,
    invoice_id - prev_id - 1 AS missing_count
FROM (
    SELECT invoice_id,
           LAG(invoice_id, 1, 0) OVER (ORDER BY invoice_id) AS prev_id
    FROM invoices
) t
WHERE invoice_id - prev_id > 1;

-- Find date gaps: days with no sales
WITH calendar AS (
    -- Generate all dates in range
    WITH RECURSIVE dates AS (
        SELECT MIN(sale_date) AS d FROM sales
        UNION ALL
        SELECT DATE_ADD(d, INTERVAL 1 DAY)
        FROM dates WHERE d < (SELECT MAX(sale_date) FROM sales)
    )
    SELECT d FROM dates
)
SELECT c.d AS missing_date
FROM calendar c
LEFT JOIN sales s ON c.d = s.sale_date
WHERE s.sale_date IS NULL;

Consecutive Wins — Sports/Game Interview Pattern

-- Find longest consecutive win streak for each team
WITH results AS (
    SELECT team, match_date, result,
           ROW_NUMBER() OVER (PARTITION BY team ORDER BY match_date) AS rn,
           ROW_NUMBER() OVER (PARTITION BY team, result ORDER BY match_date) AS rn2
    FROM match_results
),
grouped AS (
    SELECT team, result, rn - rn2 AS grp,
           COUNT(*) AS streak_len
    FROM results
    WHERE result = 'Win'
    GROUP BY team, result, rn - rn2
)
SELECT team, MAX(streak_len) AS longest_win_streak
FROM grouped
GROUP BY team
ORDER BY longest_win_streak DESC;

FAQs

What is the gaps and islands problem in SQL?

Finding runs of consecutive values (islands), such as login streaks, or missing values between them (gaps), such as skipped invoice numbers.

How does the ROW_NUMBER trick work?

For consecutive values, value minus row number stays constant, so that difference becomes a group key; GROUP BY it to get each island's start, end and length.

How do you find consecutive login days in SQL?

Remove duplicate dates per user, subtract ROW_NUMBER() (in days) from each date, group by user and that result, and keep groups with the required count.