Streaks and gaps

Group consecutive rows into streaks, and find the gaps between them, with one subtraction trick.

“What’s our longest run of months with a win?” “Which days was the system down, in unbroken stretches?” “How many consecutive days has this user logged in?” These are gaps and islands problems. Islands are runs of consecutive values. Gaps are the holes between them. They’re a favorite interview question, because the standard answer is short but not obvious.

The data

Months with at least one won deal:

2025-03, 2025-05, 2025-06, 2025-08, 2025-09, 2025-11, 2025-12, 2026-01, 2026-02, 2026-03, 2026-04, 2026-06, 2026-09, 2026-10

By eye, the longest streak runs from November 2025 to April 2026: six months in a row, across a year boundary. The goal is to get SQL to find every streak like that.

Step 1: a number that counts up by one

Consecutive months need to be consecutive numbers. Month numbers alone restart every January, so December (12) and January (1) wouldn’t look consecutive. Multiply the year by 12 and add the month instead:

SELECT DISTINCT
       strftime('%Y-%m', closed_at) AS month,
       CAST(strftime('%Y', closed_at) AS INTEGER) * 12
         + CAST(strftime('%m', closed_at) AS INTEGER) AS month_num
FROM opportunities
WHERE is_won = 1;

December 2025 becomes 24312 and January 2026 becomes 24313, one apart, as they should be. DISTINCT gives each month once, however many deals it had.

Step 2: the trick

Number the rows in order with ROW_NUMBER (lesson 30), then subtract that from the month number:

Inside a streak, both columns go up by 1 each row, so their difference stays the same. When a month is skipped, the month number jumps by 2 but the row number only by 1, so the difference changes. Every streak gets its own constant key. The key’s actual value means nothing. It’s only used to group.

Step 3: group by the key

WITH win_months AS (
    SELECT DISTINCT
           strftime('%Y-%m', closed_at) AS month,
           CAST(strftime('%Y', closed_at) AS INTEGER) * 12
             + CAST(strftime('%m', closed_at) AS INTEGER) AS month_num
    FROM opportunities
    WHERE is_won = 1
),
keyed AS (
    SELECT month,
           month_num - ROW_NUMBER() OVER (ORDER BY month_num) AS streak_key
    FROM win_months
)
SELECT MIN(month) AS first_month,
       MAX(month) AS last_month,
       COUNT(*)   AS months
FROM keyed
GROUP BY streak_key
ORDER BY first_month;

Six streaks, the longest six months. The same three steps work for days, weeks or any sequence: get a number that goes up by exactly one per step, subtract ROW_NUMBER, then group.

The gaps: what’s missing between streaks

To find the gaps instead, compare each month with the next one using LEAD (lesson 31):

WITH win_months AS (
    SELECT DISTINCT
           strftime('%Y-%m', closed_at) AS month,
           CAST(strftime('%Y', closed_at) AS INTEGER) * 12
             + CAST(strftime('%m', closed_at) AS INTEGER) AS month_num
    FROM opportunities
    WHERE is_won = 1
)
SELECT month,
       LEAD(month) OVER (ORDER BY month_num)                 AS next_win,
       LEAD(month_num) OVER (ORDER BY month_num) - month_num - 1 AS empty_months
FROM win_months;

Any row with empty_months above 0 sits right before a gap. June 2026 shows 2: no wins in July or August.

Common mistakes

Dialect notes

The trick is the same everywhere. Only the month number changes: YEAR(d) * 12 + MONTH(d) in SQL Server and MySQL, and EXTRACT(YEAR FROM d) * 12 + EXTRACT(MONTH FROM d) in PostgreSQL. For streaks of days, PostgreSQL can subtract an integer from a date directly: d - ROW_NUMBER() OVER (ORDER BY d)::int gives a constant date for each streak.

Snowflake notes

In Snowflake, the streak key for days is DATEADD('day', -ROW_NUMBER() OVER (ORDER BY d), d), and for months, DATEADD('month', -ROW_NUMBER() OVER (ORDER BY m), m), which avoids the year-times-12 step. CONDITIONAL_TRUE_EVENT and CONDITIONAL_CHANGE_EVENT are Snowflake-only window functions that number islands directly, starting a new number whenever a condition is true or a value changes.

More in the field guide: Gaps & islands.

Try it

Find every streak of back-to-back months that had at least one won deal, across both years. Return the first month, the last month and the streak’s length in months, oldest first. Your result should match the table above.

Write it in the sandbox below, then press Check answer.

Back to the top of the lesson