Find the Missing Ids

An interviewer asks: “Invoices are numbered one after another, but some were deleted. Which numbers are missing?” The idea: put each id beside the next id. Where the next id is more than one bigger, the numbers between them are missing.

LEAD is a window function that reads a column from the next row, in the order you give. The last row has no next row, so it gets NULL. Here it puts each order's date beside the next order's date:

SELECT id, ordered,
       LEAD(ordered) OVER (ORDER BY ordered) AS next_order
FROM orders

You can't filter on a window column in the same SELECT: WHERE runs before window functions are computed. So compute the next id in a WITH query, then filter in the main query that reads from it.

For this lesson, a hidden setup adds an invoices table to shop.db:

id   | issued     | amount
1001 | 2026-03-02 | 34
1002 | 2026-03-15 | 29
1003 | 2026-04-05 | 41
1005 | 2026-04-18 | 27
1006 | 2026-05-07 | 73
1009 | 2026-05-21 | 9
1010 | 2026-05-30 | 62
1012 | 2026-06-02 | 45

Your query must print each hole as a range, from its first missing id to its last:

gap_start | gap_end
1004 | 1004
1007 | 1008
1011 | 1011
(3 rows)

Your task: in query, write a WITH query named with_next that gives each invoice's id and the next id, named next_id. Below it, select id + 1 as gap_start and next_id - 1 as gap_end from with_next, and keep only the rows where next_id is more than id + 1.

Assignment
Follow the Coding Tutorial to build the missing-ids query, one clause per line.

Hint
With no hole, the next id is exactly id + 1. The last invoice's next_id is NULL, and a comparison with NULL is never true, so that row drops out by itself. Each clause is its own string inside the brackets of query; end each one with a space.