Visualize SQL | Puzzle #9 Available Seats Part2

Beginning

This article is based on 『SQL Puzzle and Answers』
　⇒ #9 Available Seats
It is better with this book to read this article.

In the previous article,
finding empty seats(missing numbers) from numbers

This time
rewrite previous SQL like ・・
fill empty number with the most recent seated number
　⇒ output image is below

▼ Output Image

【step】
① find empty seats
② find recent seated number from Restaurant table
③ fill empty number with these numbers

I wish this could help you to learn SQL techniques

check working with PostgreSQL

Restaurant Table & Data

SQL

CREATE TABLE Restaurant(
   seat integer -- occupied seat number
);

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);

Create conecutive numbers(1~20)

SQL

-- conecutive numbers(1~20)
WITH RECURSIVE seq_0 AS (
	SELECT 1 AS seq
	UNION ALL
	SELECT seq + 1 FROM seq_0 WHERE seq < 20
)

SELECT * FROM seq_0

-- Output
-- 1
-- 2
-- ・
-- ・
-- 20

Create conecutive numbers
using Recursive Query
　⇒ check below article

Creating base table

SQL

WITH RECURSIVE seq_0 AS (
	SELECT 1 AS seq
	UNION ALL
	SELECT seq + 1 FROM seq_0 WHERE seq < 20
)

SELECT b1.seq, b2.seat
FROM seq_0 b1
LEFT JOIN Restaurant b2 ON b1.seq = b2.seat

ORDER BY b1.seq

Join the Restaurant table to consecutive numbers
　⇒ this is base table
　⇒ we are going to join data to this table

▼ Output

Edit Joined data①

SQL

WITH RECURSIVE seq_0 AS (
	SELECT 1 AS seq
	UNION ALL
	SELECT seq + 1 FROM seq_0 WHERE seq < 20
)

,seq_1 AS (
	SELECT b1.seq, b2.seat
	FROM seq_0 b1
	LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
	ORDER BY b1.seq
)

SELECT c1.*
 ,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
 ,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
FROM seq_1 c1

ORDER BY c1.seq

▼ Output

Add ARRAY_AGG() and MAX() in SELECT clause

The work here is preparing to fill with number
if current row is empty(NULL)

ARRAY_AGG(c2.seq)
　⇒ Get all seq numbers
　　 that are smaller than c1.seq from seq_1
　⇒ a set of numbers
　⇒ This is for checking to get the max number
MAX(c2.seq)
　⇒ Pick the MAX number from the set
　⇒ red letters are the numbers to fill empty

Edit Joined dat②

SQL

WITH RECURSIVE seq_0 AS (
	SELECT 1 AS seq
	UNION ALL
	SELECT seq + 1 FROM seq_0 WHERE seq < 20
)

,seq_1 AS (
	SELECT b1.seq, b2.seat
	FROM seq_0 b1
	LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
	ORDER BY b1.seq
)

SELECT c1.*
 ,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
 ,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
FROM seq_1 c1
WHERE c1.seat IS NULL -- Add

ORDER BY c1.seq

Add c1.set IS NULL to the WHERE clause
to choose only empty setas(numbers)

Now we have data with numbers in empty seats

use these numnber next

▼ Output(right side of image)

Edit Joined data③

SQL

WITH RECURSIVE seq_0 AS (
	SELECT 1 AS seq
	UNION ALL
	SELECT seq + 1 FROM seq_0 WHERE seq < 20
)

,seq_1 AS (
	SELECT b1.seq, b2.seat
	FROM seq_0 b1
	LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
	ORDER BY b1.seq
)

,seq_2 AS (
  SELECT c1.*
--  ,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
   ,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL) seq_max
  FROM seq_1 c1
  WHERE c1.seat IS NULL
)

SELECT d1.seq,d1.seat,d2.seq,d2.seq_max
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq

▼ Output(right side of image)

Data we created in Creating base table
　⇒ left side of above image(yellow)
　　 conscutive number(1~20) & seated number
　⇒ seq_1
Data we created in Edit Joined data⓶
　⇒ middle of above image(white)
　⇒ empty seats(number) have the most recent number
　⇒ seq_2

LEFT JOIN seq_1 and seq_2

SELECT d1.seq,d1.seat,d2.seq,d2.seq_max
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq

LEFT JOIN is the important part
　⇒ right side of above image
　⇒ yellow part, these rows has seated number
　⇒ blue part, these rows are empty
　　but there are numbers now(red letter)
　　⇒ used LEFT JOIN to get these rows

Almost done !
the last job is to fix SELECT statement

Final SQL

SQL

WITH RECURSIVE seq_0 AS (
	SELECT 1 AS seq
	UNION ALL
	SELECT seq + 1 FROM seq_0 WHERE seq < 20
)

,seq_1 AS (
	SELECT b1.seq, b2.seat
	FROM seq_0 b1
	LEFT JOIN Restaurant b2 ON b1.seq = b2.seat
	ORDER BY b1.seq
)

,seq_2 AS (
  SELECT c1.*
-- ,(SELECT ARRAY_AGG(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL)
  ,(SELECT MAX(c2.seq) FROM seq_1 c2 WHERE c2.seq<c1.seq AND c2.seat IS NOT NULL) seq_max
  FROM seq_1 c1
  WHERE c1.seat IS NULL
)

SELECT d1.seat,COALESCE(d1.seat,d2.seq_max) seq2 -- Edit
FROM seq_1 d1
LEFT JOIN seq_2 d2 ON d2.seq = d1.seq

▼ Output(right side of image)

Edit SELECT clause
　⇒ d1.seat,COALESCE(d1.seat,d2.seq_max) seq2
　⇒ when d1.seatt is NULL, show d2.seq_max

COALESCE() is often used

If the column you want to display is NULL,
display a different column
it's often used to avoid display empty

References

Joe Celko's SQL Puzzles and Answers