Visualize SQL | Smart Use of NULL Data

# 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.

`This is the same as『Visualize SQL | Complicated Subquery and table JOIN』`
`but this uses a different way to get the same data`

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

This SQL works with PostgreSQL

![rep_0.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/98750dbb-f9b4-bc49-18bb-aecd2a2af851.png)

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

▼ Output

![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/c9ddd5bf-7479-897c-9d47-8db5023b855a.png)

# Answer SQL
```SQL:SQL
SELECT C1.claim_id, C1.patient_name,
   CASE MIN(S1.claim_seq) WHEN 2 THEN 'AP'
                          WHEN 3 THEN 'OR'
                          WHEN 4 THEN 'SF'
                          ELSE 'C1' END
FROM
(
	(
		Claims AS C1
		INNER JOIN
		Defendants AS D1 ON C1.claim_id = D1.claim_id
	) CROSS JOIN ClaimStatusCodes AS S1
)
LEFT OUTER JOIN
	LegalEvents AS E1 ON C1.claim_id  = E1.claim_id
		AND D1.defendant_name = E1.defendant_name
		AND S1.claim_status = E1.Claim_status

WHERE E1.claim_id IS NULL
GROUP BY C1.claim_id, C1.patient_name
```

▼ Output is the same as  『 Visualize SQL | Complicated Subquery and table JOIN 』

This answer SQL joins all tables in order
INNER JOIN 『Claims』 and 『Defendants』 table
　⇒　CROSS JOIN 『ClaimStatusCodes』 table
　　⇒　LEFT JOIN 『LegalEvents』 table

This answer SQL is simplified(JOIN parts) so I fixed them a little and
explain what they do from the top INNER JOIN, CROSS JOIN to the bottom LEFT JOIN

# First INNER JOIN
```SQL:SQL
SELECT C1.*, D1.*
FROM Claims AS C1
INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
```

▼ the left side of image is the output

creating all combinations of patients and defendants for each claim using INNER JOIN

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/f2e62896-495e-d056-a87d-ab73ad7bbf95.png)

# Next CORSS JOIN
```SQL:SQL
SELECT C1.*, D1.*,S1.*
FROM Claims AS C1
INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
CROSS JOIN ClaimStatusCodes AS S1
```

▼ the left side of image is the output

CROSS JOIN 『ClaimStatusCodes』 table to the output of 『first INNER JOIN』

Join claim_seq(1-4) numbers that ClaimStatusCodes has to all defendants
　⇒　this is the original data that we can see all status of all claims

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/14ae4107-d81e-e018-7925-05bcd19f3086.png)

# Last LEFT JOIN
```SQL:SQL
SELECT a1.*,E1.*
FROM
(
	SELECT C1.*, D1.defendant_name,S1.*
	FROM Claims AS C1
	INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
	CROSS JOIN ClaimStatusCodes AS S1
) AS a1
LEFT JOIN LegalEvents AS E1 ON a1.claim_id = E1.claim_id
  AND a1.defendant_name = E1.defendant_name
  AND a1.claim_status = E1.Claim_status
```

▼ LEFT JOIN ongoing claim status(LegalEvents) to the output of 『next CROSS JOIN』

join conditions are below
　　[1] the same claim_id
　　　　⇒　matching the same claim
　　[2] the same defendant_name
　　　　⇒　matching the same defendant
　　[3] the same claim_status
　　　　⇒　matching the same claim status

Its output rows number is the same as 『Next CROSS JOIN』
We can see which status are not finished yet from this output

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/76681936-bb0d-02b5-fbd4-cb09558bbc74.png)

# Taking Out NULL Data
```SQL:SQL
SELECT a1.*,E1.*
FROM
(
	SELECT C1.*, D1.defendant_name,S1.*
	FROM Claims AS C1
	INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
	CROSS JOIN ClaimStatusCodes AS S1
) AS a1
LEFT JOIN LegalEvents AS E1 ON a1.claim_id = E1.claim_id
  AND a1.defendant_name = E1.defendant_name
  AND a1.claim_status = E1.Claim_status
WHERE E1.claim_id IS NULL ------------- add this condition
```

▼ the right side of image is the output

![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/dc58d03f-b8e2-d5b4-68f4-3a343846faf2.png)

# Answer SQL
```SQL:SQL
SELECT a1.claim_id,
       a1.patient_name,
		array_agg(a1.claim_seq),
		MIN(a1.claim_seq),
		CASE MIN(a1.claim_seq) WHEN 2 THEN 'AP'
                               WHEN 3 THEN 'OR'
                               WHEN 4 THEN 'SF'
                               ELSE 'CL' END AS claim_status
FROM
(
	SELECT C1.*, D1.defendant_name,S1.*
	FROM Claims AS C1
	INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id
	CROSS JOIN ClaimStatusCodes AS S1
) AS a1
LEFT JOIN LegalEvents AS E1 ON a1.claim_id = E1.claim_id
   AND a1.defendant_name = E1.defendant_name
   AND a1.claim_status = E1.Claim_status
WHERE E1.claim_id IS NULL

GROUP BY a1.claim_id, a1.patient_name
```

▼ Output

|claim_id|patient_name|array_agg|min|claim_status|
|---|---|---|---|---|
|10|Smith|{4,3,4}|3|OR|
|20|Johns|{2,3,4,3,4}|2|AP|
|30|Brown|{2,3,4}|2|AP|

the slowest status in 『claim 10』 is OR
the slowest status in 『claim 20』 is AP
『claim 30』 has one defendant. Its current status is AP

the data that we want to take out is ・・・
the data that has the smallest claim_seq for each claim
　⇒　add GROUP BY to get the smallest claim_seq
　⇒　NULL rows has status that has not finished yet
　⇒　the next one up of the smallest NULL row is the current status
　⇒　**taking out this status in the CASE expression**

![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e23a32b7-e642-6615-e8f7-62ee9f1aeed2.png)


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