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 <font color="Blue">empty seats</font>(missing numbers) from numbers

https://qiita.com/hidesroom/items/ad4fd4a2045ad4052c90


This time
rewrite previous SQL like ・・
<font color="Blue">fill empty number</font> with the most recent seated number
　⇒ output image is below

▼ Output Image
![pic_1.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d5a9186f-3580-4fcc-a6e6-00e05f938670.png)



:::note
【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: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: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

https://qiita.com/hidesroom/items/2a7eb6db80352f91a188


# Creating base table
```SQL: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
![pic_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/fb0a747d-6037-4c7f-9341-c9d6dc8dcbc9.png)


# Edit Joined data①
```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
)

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
![pic_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/49d88aa0-9987-4053-b356-428eb4cd7c52.png)

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
　⇒ <font color="red">red letters</font> are the numbers to fill empty




# Edit Joined dat②
```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
)

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 <font color="deeppink">c1.set IS NULL</font> 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)

![pic_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/40054e3c-a6dc-47d7-88b8-d0281ae5be31.png)



# Edit Joined data③
```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.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)
![pic_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/340cc8aa-650c-44ba-a074-29d47b4aead3.png)




Data we created in `Creating base table`
　⇒ left side of above image(yellow)
　　 conscutive number(1~20) & seated number
　⇒ <font color="orange">seq_1</font>
Data we created in `Edit Joined data⓶`
　⇒ middle of above image(white)
　⇒ empty seats(number) have the most recent number
　⇒ <font color="blue">seq_2</font>
 
**LEFT JOIN** seq_1 and seq_2
> SELECT d1.seq,d1.seat,d2.seq,d2.seq_max
> FROM <font color="orange">seq_1</font> d1
> LEFT JOIN <font color="blue">seq_2</font> 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)
 　　⇒ <font color="deeppink">used LEFT JOIN to get these rows</font>

Almost done !
the last job is to fix SELECT statement

# Final SQL
```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)
![pic_7.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/43ca9921-9351-4b29-8083-5de7e3c503b6.png)


Edit SELECT clause
　⇒ d1.seat,COALESCE(d1.seat,d2.seq_max) seq2
　⇒ when <font color="orange">d1.seatt</font> is NULL, show <font color="blue">d2.seq_max</font>
  
> 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](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)