Visualize SQL | Complicated Subquery and table JOIN

# Beginning
This technique is based on SQL Puzzle and Answers # 12 claims status
It is better with 『SQL Puzzle and Answers』 to read this article.

This quiz it to find the status of claims.

There are thee claims their ids are 『10』『20』『30』
I call them like 『claim 10』『claim 20』『claim 30』 here
Each claim has a different status depending on defendants

We are going to take out the lowest status in the same claims

![rep_0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/bc47ac6d-d74f-2b05-c465-182052f58496.png)

**I checked that this sql works by PostgreSQL**

# Tables and Data
```SQL:SQL
-- a list of patients
CREATE TABLE Claims(
	claim_id integer,
	patient_name text
);

INSERT INTO Claims VALUES(10,'Smith');
INSERT INTO Claims VALUES(20,'Johns');
INSERT INTO Claims VALUES(30,'Brown');

-- a list of defendants
CREATE TABLE Defendants(
	claim_id integer,
	defendant_name text
);

INSERT INTO Defendants VALUES(10,'Johnson');
INSERT INTO Defendants VALUES(10,'Mayer');
INSERT INTO Defendants VALUES(10,'Dow');
INSERT INTO Defendants VALUES(20,'Baker');
INSERT INTO Defendants VALUES(20,'Mayer');
INSERT INTO Defendants VALUES(30,'Johnson');

-- claim history of each defendants
CREATE TABLE LegalEvents(
	claim_id integer,
	defendant_name text,
	claim_status text,
	change_date text
);

INSERT INTO LegalEvents VALUES(10,'Johnson','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(10,'Johnson','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(10,'Johnson','SF','1994-03-01');
INSERT INTO LegalEvents VALUES(10,'Johnson','CL','1994-04-01');
INSERT INTO LegalEvents VALUES(10,'Mayer','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(10,'Mayer','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(10,'Mayer','SF','1994-03-01');
INSERT INTO LegalEvents VALUES(10,'Dow','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(10,'Dow','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(20,'Mayer','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(20,'Mayer','OR','1994-02-01');
INSERT INTO LegalEvents VALUES(20,'Baker','AP','1994-01-01');
INSERT INTO LegalEvents VALUES(30,'Johnson','AP','1994-01-01');

-- claim status has four levels
CREATE TABLE ClaimStatusCodes(
	claim_status text,
	claim_status_desc text,
	claim_seq integer
);

INSERT INTO ClaimStatusCodes VALUES('AP','Awaiting review panel',1);
INSERT INTO ClaimStatusCodes VALUES('OR','Panel opinion rendered',2);
INSERT INTO ClaimStatusCodes VALUES('SF','Suit filed',3);
INSERT INTO ClaimStatusCodes VALUES('CL','Closed',4);
```

▼ table data

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/1cb2eb7e-fce3-39f4-233e-34c928d3197a.png)


# Answer SQL
```SQL:SQL
-- The query is shifted. It is easy to see.
SELECT C1.claim_id, C1.patient_name, S1.claim_status, S1.claim_seq
FROM Claims AS C1, ClaimStatusCodes AS S1
WHERE S1.claim_seq IN ( SELECT MIN(S2.claim_seq)
						FROM ClaimStatusCodes AS S2
						WHERE S2.claim_seq IN ( SELECT MAX(S3.claim_seq)
												FROM LegalEvents AS E1,ClaimStatusCodes AS S3
												WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id
												GROUP BY E1.defendant_name
											  )
					  );
```

Let's see this query. What is this sql doing?

read it as it is ・・・・

In subquery[1]
　⇒　taking out the latest claim status that each defendant has

In the next subquery[2]
　⇒　taking the lowest claim status from ClaimStatusCodes
　⇒　using MAX(S3.claim_seq) in WHERE clause, that come from subquery[1]

This is not a long sql so that it is good practice to read
Let's read this sql little by little


Read in the order of subquery[1], subquery[2], and the most outside of SELECT statement.
**SQL is basically read from inside to outside**


![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2aae1377-c8a9-8ff6-98c3-0e1fb4c7c994.png)

# subquery[1] step1
```SQL:SQL
SELECT C1.*, S1.*
FROM Claims AS C1, ClaimStatusCodes AS S1
-- Conditions for the claim status that we want to take out
-- WHERE S1.claim_seq IN (SELECT MIN(S2.claim_seq)
--                         FROM ClaimStatusCodes AS S2
--                          ・・・・・
```
Looking at the Answer SQL, above SQL is the base data
Getting claim status using subquery[1], subquery[2] from this base data

CROSS JOIN data of Claims and ClaimStatusCodes tables is
all combinations of claim status that 『claim 10』『claim 20』『claim 30』has
　⇒　some of this combinations would be the answer
　⇒　taking out one each status from 『claim 10』『claim 20』『claim 30』that fit conditions

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e3010a96-0812-3c71-9149-b85ef716f942.png)

# subquery[1] step2
```SQL:SQL
SELECT E1.*, S3.claim_status, S3.claim_seq
FROM LegalEvents AS E1,ClaimStatusCodes AS S3
WHERE E1.claim_status = S3.claim_status
```

This CROSS JOIN data shows all claim status of each defendant so far

LegalEvents table does not have sequence numbers that tell claim status
Taking out this number from ClaimStatusCodes table using CROSS JOIN and WHERE clause
　⇒ The higher number means its claim status is higher

『claim 10』 has three defendants (Dow, Johnson, Mayer)
『claim 20』 has two defendants (Baker, Mayer)
『claim 30』 has one defendant (Jonson)

Even in the same claim, its status is different depending on defendants
This quiz is looking for the lowest claim status in the same claims
　⇒　the rows with ★ is what we are going to take out

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/24c83705-4374-ed69-d3b8-c3e5a0118754.png)

# subquery[1] step3
```SQL:SQL
SELECT C1.claim_id, C1.patient_name, S1.claim_status,S1.claim_seq,
  ( SELECT array_agg(S3.claim_seq) 
     FROM LegalEvents AS E1,ClaimStatusCodes AS S3
      WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id ),

  ( SELECT MAX(S3.claim_seq)
     FROM LegalEvents AS E1,ClaimStatusCodes AS S3
      WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id ) AS a1
FROM Claims AS C1, ClaimStatusCodes AS S1
```

▼ Output(left side of image)

Add array_agg(S3.claim_seq), MAX(S3.claim_seq) in SELECT statement

In subquery[1], taking out MAX(S3.claim_seq) then passing to subquery[2]
**What is this MAX(S3.claim_seq)?**  We are going to see in detail

array_agg(S3.claim_seq)
　⇒　a list of claim_seq for each claim_id
　⇒　there is a condition below in WHERE clause
　⇒　E1.claim_id = C1.claim_id (yellow dotted line)
　⇒　this list comes from pink dotted frame (right side of the image below)

MAX(S3.claim_seq)
　⇒　the biggest number in the 『array_agg(S3.claim_seq)』

【for example】
there are three defendants in 『claim 10』, taking out all of their claim_seq
　⇒　1,1,1,2,2,2,3,3,4
　⇒　taking out the largest number in the same claim
　⇒　left side of the image below (purple letter)
　⇒　4
　⇒　this is MAX(S3.claim_seq) 

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/799ec878-a06e-90b2-2842-8c7ae292f47e.png)

Did you see the whole picture of this sql somehow・・・?
**But it does not fit the conditions yet**, modify the above SQL and check it again

# subquery[1] step4
```SQL:SQL
SELECT C1.claim_id, C1.patient_name, S1.claim_status,
  (	
      SELECT array_agg(b1.max_seq)
      FROM
      ( -- subquery[1]
          SELECT MAX(S3.claim_seq) AS max_seq
          FROM LegalEvents AS E1,ClaimStatusCodes AS S3
          WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id 
          GROUP BY E1.defendant_name
      ) AS b1
  ) AS c1

FROM Claims AS C1, ClaimStatusCodes AS S1
```

▼ Output

Add GROUP BY E1.defendant_name
Then taking out array_agg(b1.max_seq) from subquery[1]

why・・?

For example, 『claim 10』 has three defendants(Dow,Johonson,Mayer)
they have a different claim status so should be checked individually

By adding 『GROUP BY』 clause
claim_seq which comes from ClaimStatusCodes is not same as 『subquery[1] step3』
　⇒　**this output is passed to subquery[2]**

case 『claim 10』
　⇒　taking out the max number of claim_seq from Dow, Johnson and Mayer
　⇒　2, 4, 3 are passing to subquery[2]

case 『claim 20』
　⇒　taking out the max number of claim_seq from Baker, Mayer
　⇒　1, 2 are passing to subquery[2]

case 『claim 30』
　⇒　taking out the max number of claim_seq from Johnson
　⇒　1 is passing to subquery[2]


![rep_8.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/23b62d99-6a5e-f998-64de-8aa4812eb9d2.png)

# subquey[2]
```SQL:SQL
SELECT C1.claim_id, C1.patient_name, S1.claim_status,
  ( -- subquey[2]
      SELECT array_agg(S2.claim_seq)
      FROM ClaimStatusCodes AS S2
      WHERE S2.claim_seq IN 
      ( -- subquey[1]
            SELECT MAX(S3.claim_seq) AS max_seq
            FROM LegalEvents AS E1,ClaimStatusCodes AS S3
            WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id 
            GROUP BY E1.defendant_name
      )
  ) AS c2,
  ( -- subquey[2]
      SELECT MIN(S2.claim_seq)
      FROM ClaimStatusCodes AS S2
      WHERE S2.claim_seq IN 
      ( -- subquey[1]
            SELECT MAX(S3.claim_seq) AS max_seq
            FROM LegalEvents AS E1,ClaimStatusCodes AS S3
            WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id 
            GROUP BY E1.defendant_name
      )
  ) AS c3

FROM Claims AS C1, ClaimStatusCodes AS S1
```

What is doing in subquery[2] with the data got from subquery[1]?
Let's see in SELECT statement
There are **two similar SELECT statements**, they are **c2,c3** 

c2： passing MAX(S3.claim_seq) of subquery[1] to subquery[2]
　⇒　taking out the same number(claim_seq) that MAX(S3.claim_seq) has
　　　　from ClaimStatusCodes table
　⇒　a list of this output is array_agg(S2.claim_seq)

c3: the smallest number in the array_agg(S2.claim_seq)
　⇒　MIN(S2.claim_seq)
　⇒　**this number is the value for the most outside WHERE clause**
　　　and get the claim status that fit the conditions


![rep_10.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/6f8e6f45-f752-33b5-0642-e621b026cde9.png)



# Answer SQL
```SQL:SQL
-- The query is shifted. It is easy to see.
SELECT C1.claim_id, C1.patient_name, S1.claim_status, S1.claim_seq
FROM Claims AS C1, ClaimStatusCodes AS S1
WHERE S1.claim_seq IN ( SELECT MIN(S2.claim_seq)
						FROM ClaimStatusCodes AS S2
						WHERE S2.claim_seq IN ( SELECT MAX(S3.claim_seq)
												FROM LegalEvents AS E1,ClaimStatusCodes AS S3
												WHERE E1.claim_status = S3.claim_status AND E1.claim_id = C1.claim_id
												GROUP BY E1.defendant_name
											  )
					  );
```

▼ Output

|claim_id|patient_name|claim_status|claim_seq|
|---|---|---|---|
|10|Smith|OR|2|
|20|Johns|AP|1|
|30|Brown|AP|1|

![rep_11.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/4394937e-4f33-10f8-d683-2d5f690b9aee.png)

# References
[Joe Celko's SQL Puzzles and Answers](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)