Finding gaps in sequences in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does 'finding gaps in sequences' mean in a database context?

It means identifying missing values or breaks in a series of numbers or dates that should be continuous, like missing invoice numbers or dates in a log.

intermediate

Which SQL function can help detect gaps by comparing current and next row values?

The LEAD() function lets you look at the next row's value to compare and find where the sequence breaks.

beginner

Why is it useful to find gaps in sequences in real life?

Finding gaps helps spot missing data, errors in numbering, or skipped steps, which is important for accurate reports and audits.

intermediate

What is a simple SQL approach to find missing numbers in a sequence of IDs?

Use a query that compares each ID with the next one using LEAD(), then find where the difference is more than 1 to spot missing IDs.

advanced

How can you generate a list of missing sequence numbers once gaps are found?

You can use a numbers or tally table to generate all possible values, then LEFT JOIN with your data to find which numbers are missing.

Which SQL function helps you look at the next row's value in a sequence?

A ROW_NUMBER()

B LAG()

C LEAD()

D COUNT()

LEAD() returns the next row's value, useful to compare current and next values to find gaps.

If your sequence is 1, 2, 4, 5, which number is missing?

A 6

B 2

C 5

D 3

3 is missing because the sequence jumps from 2 to 4.

What does a gap in a sequence usually indicate?

A Missing or skipped data

B Correct continuous data

C Duplicate values

D Sorted data

A gap means some expected values are missing or skipped.

Which SQL clause is often used with LEAD() to order the sequence?

A HAVING

B OVER (ORDER BY ...)

C WHERE

D GROUP BY

LEAD() requires an OVER clause with ORDER BY to define the sequence order.

To find missing sequence numbers, you can compare each number with:

A The next number in the sequence

B The first number only

C The maximum number

D The average number

Comparing each number with the next helps detect where numbers jump, indicating gaps.

Explain how you would find gaps in a sequence of numbers stored in a database table.

Think about comparing each number with the next one.

Why is it important to detect gaps in sequences in real-world data?

Consider the impact of missing data on reports.