1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

Gaps and Islands in SQL: Find Streaks and Missing Ranges

Gaps and islands is the classic SQL pattern for finding runs of consecutive values (islands) and the holes between them (gaps): login streaks, uptime periods, missing invoice numbers. Learn the ROW_NUMBER subtraction trick step by step, the LAG alternative, the duplicate trap that splits a streak in two, and how it performed on a million rows.

9 min read
Gaps and islands in SQL: find streaks and missing ranges

Key takeaways

  • An island is a run of consecutive values; a gap is the missing range between two islands.
  • The core trick: value - ROW_NUMBER() OVER (ORDER BY value) stays constant inside an island, so you can GROUP BY it.
  • A duplicate login day created a fake extra one-day island in our test until we used DISTINCT or DENSE_RANK.
  • On about one million rows, SQLite found 10,310 islands in 0.71 seconds.

“What is each user’s longest daily login streak?” “When was the service down?” “Which invoice numbers are missing?” These look like different questions, but they are all the same problem: data is ordered, and you need to find where consecutive runs start and stop. SQL folk call it gaps and islands, it appears constantly in analytics work and in SQL interviews, and once you see the trick it takes only a few lines.

All queries ran on SQLite 3.50.4. They use standard window functions and work in PostgreSQL, SQL Server, MySQL 8 and Oracle with small changes to date arithmetic, which we note but did not run on those systems.

What are gaps and islands?

Picture the days a user logged in: 1, 2, 3, then 5, 6, then 10, 11, 12, 13 November. The islands are 1 to 3, 5 to 6 and 10 to 13. The gaps are 4 November and 7 to 9 November. The same shape appears with sequence numbers, sensor readings, shift schedules and status changes.

Our login data

CREATE TABLE logins (user TEXT, day TEXT);
-- asha: 2026-11-01, 02, 03, 05, 06, 10, 10, 11, 12, 13   (10 November twice)
-- ben:  2026-11-02, 04, 05

Asha logged in twice on 10 November. Real event tables always contain duplicates like this, and they matter, as you will see.

The ROW_NUMBER subtraction trick

WITH d AS (SELECT DISTINCT user, day FROM logins),
r AS (
  SELECT user, day,
         julianday(day) - ROW_NUMBER() OVER (PARTITION BY user ORDER BY day) AS grp
  FROM d
)
SELECT user, MIN(day) AS start, MAX(day) AS finish, COUNT(*) AS days
FROM r
GROUP BY user, grp
ORDER BY user, start;

-- ('asha', '2026-11-01', '2026-11-03', 3)
-- ('asha', '2026-11-05', '2026-11-06', 2)
-- ('asha', '2026-11-10', '2026-11-13', 4)
-- ('ben',  '2026-11-02', '2026-11-02', 1)
-- ('ben',  '2026-11-04', '2026-11-05', 2)

Five islands, exactly as expected. julianday turns a date into a day number in SQLite. In PostgreSQL you would write day - ROW_NUMBER() OVER (...) * INTERVAL '1 day', and in SQL Server DATEADD(day, -ROW_NUMBER() OVER (...), day). PARTITION BY user restarts the numbering for each user so streaks never cross from one person to another.

Why it works, step by step

day row number day number minus row number
2026-11-01 1 2461344.5
2026-11-02 2 2461344.5
2026-11-03 3 2461344.5
2026-11-05 4 2461345.5
2026-11-06 5 2461345.5
2026-11-10 6 2461348.5
2026-11-11 7 2461348.5
2026-11-12 8 2461348.5
2026-11-13 9 2461348.5

Inside an island, the date and the row number both go up by exactly one per row, so their difference does not change. When a gap appears, the date jumps by more than one while the row number still moves by one, so the difference jumps too. The actual value of the difference is meaningless (the .5 is just how Julian day numbers work); all that matters is that it is the same for every row in an island and different between islands. Grouping by it gives one row per island.

The duplicate trap

-- same query, but FROM logins instead of the DISTINCT CTE
-- ('asha', '2026-11-10', '2026-11-13', 4)
-- ('asha', '2026-11-10', '2026-11-10', 1)    fake extra island

Without removing duplicates, the two rows for 10 November got row numbers 6 and 7. The first one produced a different difference from the rest of its island, so the query reported a bogus one-day island alongside the real four-day streak. The count for the real island happened to stay right, but a “number of streaks” metric would now be wrong. Always deduplicate first with SELECT DISTINCT or a GROUP BY.

DENSE_RANK: a fix in one word

julianday(day) - DENSE_RANK() OVER (PARTITION BY user ORDER BY day) AS grp
-- ... then COUNT(DISTINCT day) instead of COUNT(*)
-- same five islands as the DISTINCT version

DENSE_RANK gives equal days the same number with no gaps in the numbering, so duplicates land in the same island. Remember to count distinct days, or the duplicate inflates the island length.

The LAG method: flag the start of each island

WITH d AS (SELECT DISTINCT user, day FROM logins),
f AS (
  SELECT user, day,
    CASE WHEN julianday(day) - julianday(LAG(day) OVER (PARTITION BY user ORDER BY day)) = 1
         THEN 0 ELSE 1 END AS new_island
  FROM d
),
g AS (
  SELECT user, day, SUM(new_island) OVER (PARTITION BY user ORDER BY day) AS island
  FROM f
)
SELECT user, island, MIN(day), MAX(day), COUNT(*) FROM g
GROUP BY user, island ORDER BY user, island;

-- ('asha', 1, '2026-11-01', '2026-11-03', 3)
-- ('asha', 2, '2026-11-05', '2026-11-06', 2)
-- ('asha', 3, '2026-11-10', '2026-11-13', 4)
-- ('ben',  1, '2026-11-02', '2026-11-02', 1)
-- ('ben',  2, '2026-11-04', '2026-11-05', 2)

The second approach compares each row with the previous one using LAG. A row starts a new island when the gap from the previous row is not exactly one (or when there is no previous row, which gives NULL and falls into the ELSE). A running SUM of those flags numbers the islands 1, 2, 3. It is longer, but more flexible: you can change the rule to “a new session starts after 30 minutes of inactivity”, which the subtraction trick cannot express. The CASE WHEN guide covers the flag expression.

Finding the gaps themselves

WITH d AS (SELECT DISTINCT user, day FROM logins),
n AS (SELECT user, day, LEAD(day) OVER (PARTITION BY user ORDER BY day) AS nxt FROM d)
SELECT user, date(day, '+1 day') AS gap_start, date(nxt, '-1 day') AS gap_end,
       CAST(julianday(nxt) - julianday(day) - 1 AS INT) AS days
FROM n WHERE julianday(nxt) - julianday(day) > 1;

-- ('asha', '2026-11-04', '2026-11-04', 1)
-- ('asha', '2026-11-07', '2026-11-09', 3)
-- ('ben',  '2026-11-03', '2026-11-03', 1)

LEAD looks at the next row. Wherever the next value is more than one step away, the range between them is a gap. Date helpers like date(day, '+1 day') are covered in our SQL date functions guide.

The longest streak per user

SELECT user, MAX(days) FROM ( ...islands query... ) GROUP BY user;
-- ('asha', 4)
-- ('ben', 2)

Once islands are rows, any aggregate works on top: longest streak, current streak (the island whose end is today), average streak length or number of streaks per month.

Numbers instead of dates: missing invoice numbers

-- invoices: 1001, 1002, 1003, 1007, 1008, 1012
SELECT MIN(no), MAX(no) FROM (
  SELECT no, no - ROW_NUMBER() OVER (ORDER BY no) AS g FROM invoices
) GROUP BY g;
-- [(1001, 1003), (1007, 1008), (1012, 1012)]

SELECT no + 1, nxt - 1 FROM (
  SELECT no, LEAD(no) OVER (ORDER BY no) AS nxt FROM invoices
) WHERE nxt - no > 1;
-- [(1004, 1006), (1009, 1011)]

With integers the subtraction needs no date functions. Auditors ask for missing invoice numbers regularly. Note that gaps in an auto-increment id are normal (rolled-back inserts and deleted rows leave holes, as our auto increment guide shows), so only use this on numbers that are supposed to be continuous.

Performance on a million rows

We filled a table with the integers 0 to 999,999, removed every multiple of 97, and grouped islands with the subtraction trick:

SELECT COUNT(*) FROM (
  SELECT g FROM (SELECT day - ROW_NUMBER() OVER (ORDER BY day) AS g FROM big)
  GROUP BY g
);
-- (10310,)   0.71 s

About 990,000 rows produced 10,310 islands in 0.71 seconds with no index. The cost is one sort plus one pass, so an index on the ordered column helps most. One SQLite detail we hit: grouping directly on the window alias in the same SELECT (GROUP BY g next to ROW_NUMBER() ... AS g) fails with misuse of aliased window function g. Compute the window in a subquery or CTE first, then group in the outer query.

Real-world uses

  • Product analytics: daily active streaks, churn after a broken streak.
  • Monitoring: turning per-minute health checks into outage periods with start and end times.
  • HR and scheduling: consecutive working days, overlapping shifts.
  • Finance and audit: missing cheque or invoice numbers.
  • Testing: grouping consecutive failures of a flaky test into incidents.

Final thoughts

Gaps and islands looks intimidating until you see the trick: subtract a row number from an ordered value, and every run of consecutive values shares the same result. Deduplicate first, use LAG with a running sum when “consecutive” needs a custom rule, and use LEAD to list the gaps. With those three patterns, streaks, outages and missing numbers all become a short, fast query.

Frequently asked questions

What is the gaps and islands problem in SQL?

It is the task of finding runs of consecutive values, called islands, and the missing ranges between them, called gaps, in ordered data such as dates or sequence numbers.

How does the ROW_NUMBER method work?

Inside a run of consecutive values, both the value and its row number rise by one per row, so their difference stays constant. Grouping by that difference gives one group per island.

Why does my streak split into two islands?

Usually because of duplicate values. Each duplicate gets its own row number and shifts the difference. Remove duplicates with DISTINCT first, or use DENSE_RANK and count distinct values.

How do I find missing numbers in a sequence?

Use LEAD to compare each value with the next one. Wherever the next value is more than one higher, the numbers between them are missing.

Which is better, ROW_NUMBER or LAG?

ROW_NUMBER subtraction is shorter for strict steps of one. LAG with a running sum handles custom rules, such as a new session after 30 minutes of inactivity.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →