Baldwin Apps

Posted on Jul 11

SQL Pattern Series #13: The Gap Detection Pattern

#sql #database #tutorial #beginners

Finding what is missing in a sequence

SQL Pattern Series #13 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to detect missing values in a sequence
How LEAD() compares the current row to the next row
Why gaps often reveal important data issues
When to use the Gap Detection Pattern

Sometimes the most important rows are the ones that are not there.

A missing ID.

A missing date.

A missing event.

A skipped invoice number.

A gap in a sequence can reveal that something happened.

Or that something failed to happen.

That is where the Gap Detection Pattern becomes useful.

The Problem

Imagine a table of numbers:

id
1
2
3
7
8

At first glance, the table looks simple.

But something is missing.

The sequence jumps from 3 to 7.

That means 4, 5, and 6 are absent.

A basic query can show the rows that exist.

The Gap Detection Pattern helps you find the spaces between them.

The Gap Detection Pattern

The Gap Detection Pattern compares each row to the next row in sequence.

A common approach uses:

LEAD(column) OVER (
    ORDER BY column
)

LEAD() lets the current row see a value from a following row.

That makes it useful for detecting jumps.

Example

SELECT
    id,
    next_id
FROM (
    SELECT
        id,
        LEAD(id) OVER (
            ORDER BY id
        ) AS next_id
    FROM numbers
) t
WHERE next_id > id + 1;

This query identifies rows where the next value is not the expected next value.

If id = 3 and next_id = 7, then a gap exists.

Example Result

id	next_id
3	7

This result does not list every missing value.

It shows where the gap starts and where the next available value appears.

From this result, you know the missing values are between 3 and 7.

Why This Pattern Matters

Gaps often tell the real story.

They can reveal:

missing records
failed imports
skipped IDs
missing dates
broken event sequences
incomplete logs

In many systems, missing values are not obvious until you compare neighboring rows.

Detecting Date Gaps

The same idea can apply to dates.

For example:

SELECT
    activity_date,
    next_activity_date
FROM (
    SELECT
        activity_date,
        LEAD(activity_date) OVER (
            ORDER BY activity_date
        ) AS next_activity_date
    FROM activity_log
) t
WHERE next_activity_date > activity_date + INTERVAL '1 day';

This checks whether the next date is more than one day after the current date.

Note: Date arithmetic syntax varies by database system. The example above uses PostgreSQL-style interval syntax. SQL Server, MySQL, Oracle, and SQLite use different approaches.

Gap Detection vs Missing Match

Gap detection and missing match are related, but they are not the same pattern.

The Missing Match Pattern asks:

Which rows do not have a related row somewhere else?

The Gap Detection Pattern asks:

Which expected values are missing from a sequence?

Both patterns help reveal absence.

But they look for different kinds of absence.

A Note on Sequences

This pattern works best when there is a meaningful expected order.

For example:

invoice numbers
event numbers
dates
monthly periods
ordered checkpoints

It is less useful when gaps are normal or meaningless.

For example, many database identity columns can have gaps because of rollbacks, deletes, caching, or failed inserts.

A missing ID does not always mean something is wrong.

The question is whether the sequence is meaningful for the business problem.

When I Reach for This Pattern

I typically use the Gap Detection Pattern when:

sequence continuity matters
records should appear in order
missing dates need investigation
logs appear incomplete
expected events did not happen

Examples include:

skipped invoice numbers
missing daily snapshots
missing monthly reports
broken event sequences
incomplete audit logs

Key Takeaway

Existing rows tell you what happened.

Gaps tell you what may be missing.

The Gap Detection Pattern helps answer:

What should be here, but is not?

Sometimes the missing values are the real story.