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:
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
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.
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:
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
ON, not in WHERE — otherwise the LEFT JOIN would turn into a plain JOIN.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”:
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.
| value | ROW_NUMBER | value − ROW_NUMBER | Island |
|---|---|---|---|
| 1 | 1 | 0 | A |
| 2 | 2 | 0 | A |
| 3 | 3 | 0 | A |
| 6 | 4 | 2 | B |
| 7 | 5 | 2 | B |
| 9 | 6 | 3 | C |
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
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:
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
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:
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
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:
Gap
Take the previous event's time with
LAGand compute the difference in minutes (unixepochgives seconds).Flag
If the gap is over 30 minutes or there is no previous event (
NULL),new_session = 1, otherwise 0.Number
The running sum of the flags (
SUM(...) OVER (ORDER BY ...)) is the session number.Summary
GROUP BYthe session number: start, end, number of views.
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:
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
Clean
DISTINCT— one login per day.Islands
ROW_NUMBERper student, an anchor date,GROUP BY→ the end and length of each streak.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.
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.
DELETE FROM orders WHERE id IN (3, 4, 8);
-- find the gaps hereExpected 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.
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 > 1withLEAD(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 usedate(day, '-' || rn || ' days'). - Remove duplicates first with
DISTINCT(or useDENSE_RANK), and restart the numbering per object withPARTITION BY. - For any threshold:
LAG→ start flag → runningSUM= island number (sessions). - Periods with the same status:
ROW_NUMBER() OVER (ORDER BY t) − ROW_NUMBER() OVER (PARTITION BY status ORDER BY t).