MySQL Gaps and Islands: Find Consecutive Dates

A common family of problems: find runs of consecutive values (islands), and the holes between them (gaps).

“How many days in a row did the user log in?”
“Which invoice numbers are missing?”
“When did the server run without errors for the longest time?”

Islands: consecutive dates

query.sql

SQL

CREATE TABLE activity (user_name VARCHAR(10), day DATE);
INSERT INTO activity VALUES
    ('asha', '2026-07-01'), ('asha', '2026-07-02'), ('asha', '2026-07-03'),
    ('asha', '2026-07-06'), ('asha', '2026-07-07'),
    ('asha', '2026-07-10'),
    ('ben',  '2026-07-02'), ('ben',  '2026-07-03');

The trick: if the dates are consecutive, date minus row number is constant inside an island.

query.sql

SQL

SELECT user_name, day,
       ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY day) AS rn,
       DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY day) DAY) AS island_key
FROM activity
ORDER BY user_name, day;

Output

OUTPUT

+-----------+------------+----+------------+
| user_name | day        | rn | island_key |
+-----------+------------+----+------------+
| asha      | 2026-07-01 |  1 | 2026-06-30 |
| asha      | 2026-07-02 |  2 | 2026-06-30 |
| asha      | 2026-07-03 |  3 | 2026-06-30 |
| asha      | 2026-07-06 |  4 | 2026-07-02 |
| asha      | 2026-07-07 |  5 | 2026-07-02 |
| asha      | 2026-07-10 |  6 | 2026-07-04 |
| ben       | 2026-07-02 |  1 | 2026-07-01 |
| ben       | 2026-07-03 |  2 | 2026-07-01 |
+-----------+------------+----+------------+
8 rows in set

Asha’s first three days all give the same island_key (2026-06-30). The next two give another (July 3), and the last one gives another (July 6). A group of rows with the same key is a run of consecutive days. Group by it:

query.sql

SQL

SELECT user_name,
       MIN(day) AS streak_start,
       MAX(day) AS streak_end,
       COUNT(*) AS days_in_a_row
FROM (
    SELECT user_name, day,
           DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY day) DAY) AS island_key
    FROM activity
) AS t
GROUP BY user_name, island_key
ORDER BY user_name, streak_start;

Output

OUTPUT

+-----------+--------------+------------+---------------+
| user_name | streak_start | streak_end | days_in_a_row |
+-----------+--------------+------------+---------------+
| asha      | 2026-07-01   | 2026-07-03 |             3 |
| asha      | 2026-07-06   | 2026-07-07 |             2 |
| asha      | 2026-07-10   | 2026-07-10 |             1 |
| ben       | 2026-07-02   | 2026-07-03 |             2 |
+-----------+--------------+------------+---------------+
4 rows in set

Asha has streaks of 3, 2 and 1 days. The longest streak of each user:

query.sql

SQL

SELECT user_name, MAX(days_in_a_row) AS longest_streak
FROM (
    SELECT user_name, COUNT(*) AS days_in_a_row
    FROM (SELECT user_name, day, DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_name ORDER BY day) DAY) AS island_key FROM activity) AS t
    GROUP BY user_name, island_key
) AS s
GROUP BY user_name
ORDER BY user_name;

Output

OUTPUT

+-----------+----------------+
| user_name | longest_streak |
+-----------+----------------+
| asha      |              3 |
| ben       |              2 |
+-----------+----------------+
2 rows in set

If a user can appear several times on the same day, remove duplicates first with SELECT DISTINCT user_name, day.

Gaps: find the missing ranges with LAG

Use LAG to compare each date with the previous one, and report the jumps:

query.sql

SQL

SELECT user_name,
       previous_day + INTERVAL 1 DAY AS gap_starts,
       day - INTERVAL 1 DAY AS gap_ends,
       DATEDIFF(day, previous_day) - 1 AS missing_days
FROM (
    SELECT user_name, day, LAG(day) OVER (PARTITION BY user_name ORDER BY day) AS previous_day FROM activity
) AS t
WHERE DATEDIFF(day, previous_day) > 1
ORDER BY user_name, gap_starts;

Output

OUTPUT

+-----------+------------+------------+--------------+
| user_name | gap_starts | gap_ends   | missing_days |
+-----------+------------+------------+--------------+
| asha      | 2026-07-04 | 2026-07-05 |            2 |
| asha      | 2026-07-08 | 2026-07-09 |            2 |
+-----------+------------+------------+--------------+
2 rows in set

Asha was absent on July 4 and 5 (2 days), and on July 8 and 9 (2 days).

Missing numbers in a sequence

Invoices should be numbered without holes:

query.sql

SQL

CREATE TABLE invoices (number INT PRIMARY KEY);
INSERT INTO invoices VALUES (1001), (1002), (1003), (1006), (1007), (1010);

SELECT previous_number + 1 AS missing_from, number - 1 AS missing_to
FROM (SELECT number, LAG(number) OVER (ORDER BY number) AS previous_number FROM invoices) AS t
WHERE number - previous_number > 1
ORDER BY missing_from;

Output

OUTPUT

+--------------+------------+
| missing_from | missing_to |
+--------------+------------+
|         1004 |       1005 |
|         1008 |       1009 |
+--------------+------------+
2 rows in set

Invoices 1004 to 1005 and 1008 to 1009 are missing. Where the gap is only one number (missing_from = missing_to), a single number is missing.

Sessions: split events by inactivity

Group events into sessions: a new session starts after more than 30 minutes without activity.

query.sql

SQL

CREATE TABLE clicks (user_name VARCHAR(10), clicked_at DATETIME);
INSERT INTO clicks VALUES
    ('asha', '2026-07-01 09:00:00'), ('asha', '2026-07-01 09:10:00'), ('asha', '2026-07-01 09:25:00'),
    ('asha', '2026-07-01 14:00:00'), ('asha', '2026-07-01 14:05:00'),
    ('ben',  '2026-07-01 10:00:00');

SELECT user_name, session_no, MIN(clicked_at) AS session_start, MAX(clicked_at) AS session_end, COUNT(*) AS clicks
FROM (
    SELECT user_name, clicked_at,
           SUM(new_session) OVER (PARTITION BY user_name ORDER BY clicked_at) AS session_no
    FROM (
        SELECT user_name, clicked_at,
               CASE WHEN TIMESTAMPDIFF(MINUTE, LAG(clicked_at) OVER (PARTITION BY user_name ORDER BY clicked_at), clicked_at) > 30 THEN 1 ELSE 0 END AS new_session
        FROM clicks
    ) AS flagged
) AS numbered
GROUP BY user_name, session_no
ORDER BY user_name, session_no;

Output

OUTPUT

+-----------+------------+---------------------+---------------------+--------+
| user_name | session_no | session_start       | session_end         | clicks |
+-----------+------------+---------------------+---------------------+--------+
| asha      |          0 | 2026-07-01 09:00:00 | 2026-07-01 09:25:00 |      3 |
| asha      |          1 | 2026-07-01 14:00:00 | 2026-07-01 14:05:00 |      2 |
| ben       |          0 | 2026-07-01 10:00:00 | 2026-07-01 10:00:00 |      1 |
+-----------+------------+---------------------+---------------------+--------+
3 rows in set

The idea: mark each event that starts a new session (the gap from the previous one is above 30 minutes), then take a running sum of those marks. The running sum is the session number. This “flag then running sum” trick is the second great window-function pattern. (The first session of each user is numbered 0, because the first event has no previous event and is not flagged. The numbers only need to be different, not start at 1.)

The same island trick for numbers

For ids or sequence numbers, number - ROW_NUMBER() is the island key:

query.sql

SQL

SELECT MIN(number) AS first_number, MAX(number) AS last_number, COUNT(*) AS run_length
FROM (SELECT number, number - ROW_NUMBER() OVER (ORDER BY number) AS island_key FROM invoices) AS t
GROUP BY island_key
ORDER BY first_number;

Output

OUTPUT

+--------------+-------------+------------+
| first_number | last_number | run_length |
+--------------+-------------+------------+
|         1001 |        1003 |          3 |
|         1006 |        1007 |          2 |
|         1010 |        1010 |          1 |
+--------------+-------------+------------+
3 rows in set

Remember

Islands: subtract the row number from the value, group by the result.
Gaps: compare with LAG, keep the rows where the jump is larger than expected.
Sessions: flag the starts, take a running sum.
Make sure the values are unique per partition, or de-duplicate first.

lightbulb

Test with a tiny table

Write 8 rows with obvious runs and gaps, run the query and check the answer by hand. Gaps-and-islands queries are easy to get almost right.