Sequences: gaps and islands

Finding missing numbers and dates in a sequence, grouping consecutive rows with the ROW_NUMBER difference, building sessions with LAG and a running sum, separating periods with the same status, and writing a login streak report.

  • Advanced
  • 22 min
  • 12 questions
  • Lesson 22 of 28

In this lesson you will learn

  • Find missing ids and dates with LEAD and a recursive CTE
  • Group consecutive values into islands with the “value − ROW_NUMBER” trick
  • Split events into sessions with LAG and a running SUM
  • Build a real report of streaks, periods and date ranges

A learning app says: “You have studied 5 days in a row!” An accountant asks: “Which invoice numbers are missing?” An admin wants to know: “In which periods was the server down this month?” These three questions are one classic SQL task — gaps and islands — and it is a favourite in job interviews too. We need two tools: ROW_NUMBER, LAG and LEAD from the lesson «Window functions», and the recursive CTE from «CTEs: WITH and recursive queries».

Gap and islandDefinition

In a sorted sequence an island is a maximal run of values with no break (1, 2, 3); a gap is the missing values between two islands (4, 5). “No break” is defined by a step: 1 for integers, 1 day for dates, or, for events, for example less than 30 minutes.

Gaps: what is missing

The simplest way is to compare each row with the next one: LEAD(id) gives the next number, and if the difference is greater than 1 there is a gap in between. Let's delete the cancelled orders 4, 5 and 9 and check:

SQL
DELETE FROM orders WHERE id IN (4, 5, 9);   -- cancelled orders

SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM orders)
WHERE next_id - id > 1;
Expected output
gap_start | gap_end
4 | 5
9 | 9
Each gap is one row: its start and its end.

If every missing number should be its own row, generate all numbers from 1 to the largest id with a recursive CTE and remove those that exist (NOT EXISTS). This method can't see a “loss” at the end: the database doesn't know what should have come after 12.

SQL
DELETE FROM orders WHERE id IN (4, 5, 9);

WITH RECURSIVE ids(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM ids WHERE n < (SELECT MAX(id) FROM orders)
)
SELECT n AS missing_id
FROM ids
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE id = n);
Expected output
missing_id
4
5
9

The same idea works for dates: a calendar CTE gives every day of the period, a LEFT JOIN attaches the logins, and the days left with NULL are the missed days. In this lesson we use a logins (student_id, login_date) table: 1 — Aysel, 2 — Murad, 3 — Leyla. The days Aysel missed between 1 and 10 October:

SQL
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
  (1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
  (2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
  (2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
  (3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');

WITH RECURSIVE calendar(day) AS (
  SELECT '2025-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM calendar WHERE day < '2025-10-10'
)
SELECT c.day AS missed_day
FROM calendar AS c
LEFT JOIN logins AS l ON l.login_date = c.day AND l.student_id = 1
WHERE l.student_id IS NULL;
Expected output
missed_day
2025-10-04
2025-10-07
2025-10-08
2025-10-10
The filter is in ON, not in WHERE — otherwise the LEFT JOIN would turn into a plain JOIN.

Sign up free to keep reading

The rest of the lesson, the quiz and your notes open with an Educora account. Signing up takes a minute, and your progress is saved on all your devices.

Islands: the ROW_NUMBER difference trick

Let's number the values in ascending order. Inside an island both the value and the ROW_NUMBER grow by 1 at every step, so their difference stays constant. After a gap the value jumps further than the number, and the difference grows. So the difference is the island's “name”:

grp = value − ROW_NUMBER() OVER (ORDER BY value)

Where

value
the value in the sequence (an id, a day number)
ROW_NUMBER()
the number of the sorted row: 1, 2, 3, …
grp
the island key: the same within one island

Then GROUP BY grp collapses each island into one row: MIN is the start, MAX the end, COUNT the length.

valueROW_NUMBERvalue − ROW_NUMBERIsland
110A
220A
330A
642B
752B
963C
Islands 1–3, 6–7 and 9; gaps 4–5 and 8.

Check the trick by hand

Problem

Values: 3, 4, 5, 8, 9, 12. What is value − ROW_NUMBER, and how many islands are there?

Show solutionHide solution

Solution

1) Row numbers: 1, 2, 3, 4, 5, 6.
2) Differences: 3 − 1 = 2, 4 − 2 = 2, 5 − 3 = 2, 8 − 4 = 4, 9 − 5 = 4, 12 − 6 = 6.
3) The distinct values are 2, 4, 6 — 3 islands: 3–5 (3 values), 8–9 (2), 12 (1).

With dates we subtract the row number as days: date(login_date, '-' || rn || ' days'). For consecutive days the result is the same “anchor” date. WINDOW w AS (ORDER BY login_date) names the window once, so several functions can use it as OVER w. Aysel's logins:

SQL
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
  (1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09');

SELECT login_date,
       ROW_NUMBER() OVER w AS rn,
       date(login_date, '-' || ROW_NUMBER() OVER w || ' days') AS grp
FROM logins
WINDOW w AS (ORDER BY login_date)
ORDER BY login_date;
Expected output
login_date | rn | grp
2025-10-01 | 1 | 2025-09-30
2025-10-02 | 2 | 2025-09-30
2025-10-03 | 3 | 2025-09-30
2025-10-05 | 4 | 2025-10-01
2025-10-06 | 5 | 2025-10-01
2025-10-09 | 6 | 2025-10-03
Three different anchors — three streaks.

For all students the numbering must restart for each student (PARTITION BY student_id), and repeated logins are removed first with DISTINCT. Then GROUP BY turns every streak into a date range:

SQL
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
  (1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
  (2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
  (2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
  (3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');

WITH days AS (SELECT DISTINCT student_id, login_date FROM logins),
grouped AS (
  SELECT student_id, login_date,
         date(login_date, '-' || ROW_NUMBER() OVER (
              PARTITION BY student_id ORDER BY login_date) || ' days') AS grp
  FROM days
)
SELECT student_id, MIN(login_date) AS from_day, MAX(login_date) AS to_day,
       COUNT(*) AS days
FROM grouped
GROUP BY student_id, grp
ORDER BY student_id, from_day;
Expected output
student_id | from_day | to_day | days
1 | 2025-10-01 | 2025-10-03 | 3
1 | 2025-10-05 | 2025-10-06 | 2
1 | 2025-10-09 | 2025-10-09 | 1
2 | 2025-10-02 | 2025-10-05 | 4
2 | 2025-10-08 | 2025-10-08 | 1
3 | 2025-10-06 | 2025-10-09 | 4
SQL
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
  (2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08');

SELECT login_date,
       date(login_date, '-' || ROW_NUMBER() OVER w || ' days') AS by_row_number,
       date(login_date, '-' || DENSE_RANK() OVER w || ' days') AS by_dense_rank
FROM logins
WINDOW w AS (ORDER BY login_date)
ORDER BY login_date;
Expected output
login_date | by_row_number | by_dense_rank
2025-10-02 | 2025-10-01 | 2025-10-01
2025-10-03 | 2025-10-01 | 2025-10-01
2025-10-03 | 2025-09-30 | 2025-10-01
2025-10-04 | 2025-09-30 | 2025-10-01
2025-10-05 | 2025-09-30 | 2025-10-01
2025-10-08 | 2025-10-02 | 2025-10-03

LAG and a running sum: sessions

The ROW_NUMBER trick only works when the step is exactly 1. For page views “consecutive” is measured differently: if less than 30 minutes pass between two views, they belong to the same session. Here a second, more general method is needed:

  1. Gap

    Take the previous event's time with LAG and compute the difference in minutes (unixepoch gives seconds).

  2. Flag

    If the gap is over 30 minutes or there is no previous event (NULL), new_session = 1, otherwise 0.

  3. Number

    The running sum of the flags (SUM(...) OVER (ORDER BY ...)) is the session number.

  4. Summary

    GROUP BY the session number: start, end, number of views.

SQL
CREATE TABLE page_views (student_id INTEGER, viewed_at TEXT);
INSERT INTO page_views VALUES
  (1, '2025-10-01 09:00'), (1, '2025-10-01 09:10'), (1, '2025-10-01 09:35'),
  (1, '2025-10-01 11:00'), (1, '2025-10-01 11:20'),
  (2, '2025-10-01 10:00'), (2, '2025-10-01 10:50');

WITH gaps AS (
  SELECT student_id, viewed_at,
         (unixepoch(viewed_at) - unixepoch(LAG(viewed_at) OVER w)) / 60 AS gap_min
  FROM page_views
  WINDOW w AS (PARTITION BY student_id ORDER BY viewed_at)
),
flagged AS (
  SELECT *, CASE WHEN gap_min <= 30 THEN 0 ELSE 1 END AS new_session
  FROM gaps
)
SELECT student_id, viewed_at, gap_min, new_session,
       SUM(new_session) OVER (PARTITION BY student_id
                              ORDER BY viewed_at) AS session_no
FROM flagged
ORDER BY student_id, viewed_at;
Expected output
student_id | viewed_at | gap_min | new_session | session_no
1 | 2025-10-01 09:00 | NULL | 1 | 1
1 | 2025-10-01 09:10 | 10 | 0 | 1
1 | 2025-10-01 09:35 | 25 | 0 | 1
1 | 2025-10-01 11:00 | 85 | 1 | 2
1 | 2025-10-01 11:20 | 20 | 0 | 2
2 | 2025-10-01 10:00 | NULL | 1 | 1
2 | 2025-10-01 10:50 | 50 | 1 | 2
NULL <= 30 is not true, so each student's first view opens a new session automatically.

Islands of equal values: status periods

Sometimes an island is not “consecutive values” but “consecutive rows with the same status”: a server is checked every day, up or down. Here the difference of two numberings works: the overall ROW_NUMBER and the ROW_NUMBER within the status. Within an unbroken period of one status both grow together, so the difference is constant:

SQL
CREATE TABLE checks (day TEXT, status TEXT);
INSERT INTO checks VALUES
  ('2025-10-01', 'up'),   ('2025-10-02', 'up'),   ('2025-10-03', 'down'),
  ('2025-10-04', 'down'), ('2025-10-05', 'up'),   ('2025-10-06', 'down'),
  ('2025-10-07', 'up'),   ('2025-10-08', 'up');

WITH g AS (
  SELECT day, status,
         ROW_NUMBER() OVER (ORDER BY day)
       - ROW_NUMBER() OVER (PARTITION BY status ORDER BY day) AS grp
  FROM checks
)
SELECT status, MIN(day) AS from_day, MAX(day) AS to_day, COUNT(*) AS days
FROM g
GROUP BY status, grp
ORDER BY from_day;
Expected output
status | from_day | to_day | days
up | 2025-10-01 | 2025-10-02 | 2
down | 2025-10-03 | 2025-10-04 | 2
up | 2025-10-05 | 2025-10-05 | 1
down | 2025-10-06 | 2025-10-06 | 1
up | 2025-10-07 | 2025-10-08 | 2
grp is unique only together with the status, hence GROUP BY status, grp.

Mini project: a login streak report

  1. Clean

    DISTINCT — one login per day.

  2. Islands

    ROW_NUMBER per student, an anchor date, GROUP BY → the end and length of each streak.

  3. Metrics

    The number of streaks, the longest streak and the current streak ending on the report day (2025-10-09); 0 if there is none.

SQL
CREATE TABLE logins (student_id INTEGER, login_date TEXT);
INSERT INTO logins VALUES
  (1, '2025-10-01'), (1, '2025-10-02'), (1, '2025-10-03'),
  (1, '2025-10-05'), (1, '2025-10-06'), (1, '2025-10-09'),
  (2, '2025-10-02'), (2, '2025-10-03'), (2, '2025-10-03'),
  (2, '2025-10-04'), (2, '2025-10-05'), (2, '2025-10-08'),
  (3, '2025-10-06'), (3, '2025-10-07'), (3, '2025-10-08'), (3, '2025-10-09');

WITH days AS (SELECT DISTINCT student_id, login_date FROM logins),
streaks AS (
  SELECT student_id, MAX(login_date) AS end_day, COUNT(*) AS len
  FROM (SELECT student_id, login_date,
               date(login_date, '-' || ROW_NUMBER() OVER (
                    PARTITION BY student_id ORDER BY login_date) || ' days') AS grp
        FROM days)
  GROUP BY student_id, grp
)
SELECT s.first_name,
       COUNT(*) AS streaks,
       MAX(k.len) AS longest,
       COALESCE(MAX(CASE WHEN k.end_day = '2025-10-09'
                         THEN k.len END), 0) AS current_streak
FROM streaks AS k
JOIN students AS s ON s.id = k.student_id
GROUP BY k.student_id
ORDER BY longest DESC, s.first_name;
Expected output
first_name | streaks | longest | current_streak
Leyla | 1 | 4 | 4
Murad | 2 | 4 | 0
Aysel | 3 | 3 | 1

Let's read the report: Leyla has logged in 4 days without a break — the app can congratulate her. Murad's record is also 4 days, but his last login was on the 8th and his current streak is 0: time to send him a reminder. Aysel has 3 streaks and a current streak of 1 day. All the logic is one SQL query, and it works for any number of students.

Exercise

Orders 3, 4 and 8 have been deleted. Use LEAD to find the gaps in the ids: gap_start and gap_end for each gap, sorted by gap_start.

Exercise · SQL
DELETE FROM orders WHERE id IN (3, 4, 8);

-- find the gaps here
Expected output
gap_start | gap_end
3 | 4
8 | 8

Exercise

From the checks table in the starter code, list the periods when the server was down: from_day, to_day, days, sorted by from_day.

Exercise · SQL
CREATE TABLE checks (day TEXT, status TEXT);
INSERT INTO checks VALUES
  ('2025-10-01', 'up'),   ('2025-10-02', 'up'),   ('2025-10-03', 'down'),
  ('2025-10-04', 'down'), ('2025-10-05', 'up'),   ('2025-10-06', 'down'),
  ('2025-10-07', 'up'),   ('2025-10-08', 'up');

-- periods with status = 'down'
Expected output
from_day | to_day | days
2025-10-03 | 2025-10-04 | 2
2025-10-06 | 2025-10-06 | 1

Remember

  • An island is an unbroken run; a gap is what is missing between islands; “unbroken” is defined by a step (1, one day, 30 min).
  • Gaps: next_id - id > 1 with LEAD(id); one row per missing value — a recursive CTE or a calendar + LEFT JOIN.
  • Islands: value − ROW_NUMBER() is constant within an island; for dates use date(day, '-' || rn || ' days').
  • Remove duplicates first with DISTINCT (or use DENSE_RANK), and restart the numbering per object with PARTITION BY.
  • For any threshold: LAG → start flag → running SUM = island number (sessions).
  • Periods with the same status: ROW_NUMBER() OVER (ORDER BY t) − ROW_NUMBER() OVER (PARTITION BY status ORDER BY t).
Spotted a mistake or something inaccurate in this lesson?

Check yourself

12 questions. Every correct answer earns XP.

Question 1 / 12

Question 1 / 12. What is an “island”?