Visualize SQL | Find blank numbers in numbers (part 1)

Beginning

This technique is based on SQL Puzzle and Answers # 9 available seats
It is better with 『SQL Puzzle and Answers』 to read this article.

rewrite the same quiz(2025/02/24)

Because it says the idea of algorithm and
You can check what each quiz is intended for ・・・

This puzzle is to find numbers that are already filled with numbers and blank numbers in table
thinking like occupied seats and open seat in a restaurant.

There are 20 seats in a restaurant, and number of 1,2,3,8,9,10,16,18,19 seats are occupied.
Find the start and the end number of open seats.

Table and data

SQL

CREATE TABLE Restaurant(
   seat integer
);

INSERT INTO Restaurant VALUES(1);
INSERT INTO Restaurant VALUES(2);
INSERT INTO Restaurant VALUES(3);
INSERT INTO Restaurant VALUES(8);
INSERT INTO Restaurant VALUES(9);
INSERT INTO Restaurant VALUES(10);
INSERT INTO Restaurant VALUES(16);
INSERT INTO Restaurant VALUES(18);
INSERT INTO Restaurant VALUES(19);

Find start and end number of open seat

SQL

-- start number
WITH Firstseat AS (
	SELECT (seat + 1) AS seat FROM Restaurant
    WHERE (seat + 1) NOT IN (SELECT seat FROM Restaurant)
	AND (seat + 1) < 1001
)

SELECT * FROM Firstseat

-- 4
-- 11
-- 17
-- 20

SQL

-- end number
WITH Lastseat AS (
	SELECT (seat - 1) AS seat FROM Restaurant
	WHERE (seat - 1) NOT IN (SELECT seat FROM Restaurant)
	AND (seat - 1) > 0
)

SELECT * FROM Lastseat

-- 7
-- 15
-- 17

『seat』 is occupied number.
find 『seat + 1』 numbers that are not in the Restaurant table
　⇒ start number of open seat (left side below image)

find 『seat - 1』 numbers that are not in the Restaurant table
　⇒ end number of open seat (right side below image)

Combine the above SQL into one

SQL

-- start numbers and end numbers of open seats
WITH Firstseat AS (											
	SELECT (seat + 1) AS seat FROM Restaurant
    WHERE (seat + 1) NOT IN (SELECT seat FROM Restaurant)
	AND (seat + 1) < 1001  -- the lower limit                   
),
Lastseat AS (
	SELECT (seat - 1) AS seat FROM Restaurant
	WHERE (seat - 1) NOT IN (SELECT seat FROM Restaurant)
	AND (seat - 1) > 0 -- the upper limit
)
SELECT F1.seat AS start, L1.seat AS finish,
	(SELECT MIN(L2.seat) FROM Lastseat AS L2 WHERE F1.seat <= L2.seat),
	(SELECT array_agg(L2.seat) FROM Lastseat AS L2 WHERE F1.seat <= L2.seat)
FROM Firstseat AS F1, Lastseat AS L1

4 is the start number. The minimum number 7 in {7,15,17} is the end number.
11 is the start number. The minimum number 15 in {15,17} is the end number.
17 is the start number. The minimum number 17 in {17} is the end number.
20 is the start number. No L2.seat greater than 20. ★

Answer SQL

SQL

WITH Firstseat AS (
	SELECT (seat + 1) AS seat FROM Restaurant
    WHERE (seat + 1) NOT IN (SELECT seat FROM Restaurant)
	AND (seat + 1) < 1001 -- the lower limit            
),
Lastseat AS (
	SELECT (seat - 1) AS seat FROM Restaurant
	WHERE (seat - 1) NOT IN (SELECT seat FROM Restaurant)
	AND (seat - 1) > 0  -- the upper limit
)
SELECT F1.seat AS start, L1.seat AS finish
FROM Firstseat AS F1, Lastseat AS L1
WHERE L1.seat = (SELECT MIN(L2.seat) FROM Lastseat AS L2 WHERE F1.seat <= L2.seat)

『start』 is open start number, 『finish』 is open end number

start	finish
4	7
11	15
17	17

References

Joe Celko's SQL Puzzles and Answers