Finding gaps in sequences in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all IDs from the table.

SQL

SELECT [1] FROM sequence_table;

Drag options to blanks, or click blank then click option'

Aid

B*

Ccount

Dvalue

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting all columns (*) instead of just the sequence column.

Using a column name that does not exist.

The column id holds the sequence numbers we want to check.

2fill in blank

medium

Complete the code to find the next ID after the current one using LEAD function.

SQL

SELECT id, [1] OVER (ORDER BY id) AS next_id FROM sequence_table;

Drag options to blanks, or click blank then click option'

ALAG(id)

BMAX(id)

CFIRST_VALUE(id)

DLEAD(id)

Attempts:

3 left

💡 Hint

Common Mistakes

Using LAG which looks backward.

Using aggregate functions like MAX which do not return next row.

The LEAD function returns the next row's value in the ordered sequence.

3fill in blank

hard

Fix the error in the code to find gaps by comparing current and next IDs.

SQL

SELECT id FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM sequence_table) sub WHERE next_id - id [1] 1;

Drag options to blanks, or click blank then click option'

A>

B<

C=

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which only finds no gap.

Using '<' which finds smaller differences.

We want to find where the gap is bigger than 1, so next_id - id > 1.

4fill in blank

hard

Fill both blanks to generate missing IDs between gaps.

SQL

SELECT id + [1] AS missing_id FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM sequence_table) sub, generate_series(1, [2]) gs WHERE next_id - id > 1;

Drag options to blanks, or click blank then click option'

Ags

B1

Cnext_id - id - 1

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Starting series at 0 which duplicates existing IDs.

Using the full difference instead of difference minus 1.

We add the series number starting at 1 up to the gap size minus 1 to find missing IDs.

5fill in blank

hard

Fill all three blanks to list all missing IDs in the sequence.

SQL

SELECT id + [1] AS missing_id FROM (SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id FROM sequence_table) sub, generate_series([2], [3]) gs WHERE next_id - id > 1;

Drag options to blanks, or click blank then click option'

Ags

B1

Cnext_id - id - 1

D0

Attempts:

3 left

💡 Hint

Common Mistakes

Starting series at 0 but adding 1 to id causing off-by-one errors.

Using wrong offset causing duplicate or missing IDs.

We generate the series from 1 to next_id - id - 1 and add gs to id to get the missing IDs: id + 1, id + 2, ..., next_id - 1.