初めに

この問題は、SQLパズル #12 訴訟の進行状況を参考にしています
手元に『SQL パズル』があれば問題の詳細が記載されているのでよりわかりやすいです

この問題は裁判の進行状況を確認する問題です

『可視化 SQL | 複雑なサブクエリとテーブル JOIN』 と 同じ内容
・・・ですが、アプローチが違います

裁判は３つあって、claim_id が『10』『20』『30』としています
この記事では裁判10、裁判20、裁判30　としています
各裁判には被告人がいて、それぞれ進行状況が違います

取出したいデータは同じ claim_id の裁判で、進行状況が一番遅い状態の裁判となります

PostgreSQL で動作確認しています

テーブルとデータ

SQL

-- 裁判の原告の一覧
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');

-- 裁判の被告人の一覧
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');

-- 裁判の進行状況が確認できるデータ
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');

-- 進行状況は4段階に分かれている
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);

▼ テーブルデータ

回答 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

▼ 出力結果は『複雑なサブクエリとテーブル JOIN』と同じです

この回答SQLは全テーブルを順番に結合しています
『Claims』と『Defendants』を INNER JOIN
　　⇒　『ClaimStatusCodes』を CROSS JOIN
　　　⇒　『LegalEvents』を LEFT JOIN

本に記載されているSQLは記述が簡略化されています(JOIN部分)
すこし手直しして、上から順番に解説していきます

最初の INNER JOIN

SQL

SELECT C1.*, D1.*
FROM Claims AS C1
INNER JOIN Defendants AS D1 ON C1.claim_id = D1.claim_id

▼画像左側が出力です

原告が起こした『裁判 10』『裁判 20』『裁判 30』で
訴えられている被告人の組合せを INNER JOIN で作成している

次の CORSS JOIN

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

▼画像左側が出力です

最初の INNER JOIN したでーたに、『ClaimStatusCodes』テーブルを CROSS JOIN します

これは、『裁判 10』『裁判 20』『裁判 30』には複数の被告人がいて
同じ裁判でも被告人によって進行状況が違います

ClaimStatusCodes が持っている進捗状況を示す1 ~ 4 番の claim_seq を
各被告の裁判に結合します
　⇒　全ての裁判の全ての進行状況が分かる元データを作ります

最後の LEFT JOIN

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

▼ 『次のCROSS JOIN』に現在進行している裁判データ(LegalEvents)を結合します

結合する時の条件は
　　[1] 同じ claim_id
　　　　⇒　同じ裁判を紐づける
　　[2] 同じ defendant_name
　　　　⇒　同じ裁判で、同じ被告人を紐づける
　　[3] 同じ claim_status
　　　　⇒　同じ裁判で、同じ被告人で、裁判の進行状況を紐づける

結合の条件が3つありますので、結合の後の行数は『次のCROSS JOIN』と同じです
元データに対して、進行中の裁判状況を紐づけしています
ここから空白のデータのある行は、まだ完了していないステータスだと見てわかります

NULL となっているデータの取出し

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 ------------- この条件を追加

▼ 画像右側が出力結果

回答 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

▼ 出力結果

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

『裁判10』の中で一番遅い進行の遅いステータスは OR
『裁判20』の中で一番遅い進行の遅いステータスは AP
『裁判30』は被告人が一人なので、ステータスは AP

空白のデータの一覧に対して、 claim_id と patient_name で GROUP BY しています
これは各裁判での進行状況を集計する為です。

GROUP BY claim_id だけでもいいかと思いますがおそらく原告の名前もそのまま使いたかったのかと思います

取出したいのは、各裁判での空白データの中で、一番小さい claim_seq を持つ行です
　⇒　GROUP BY を追加して、同じ裁判内で最小の claim_seq を取出す
　⇒　NULL となっている行はまだ完了していないステータスです
　⇒　このNULLデータで一番小さいステータスの1つ上が直近のステータスとなります
　⇒　この処理を CASE文で対応しています

参考文献

SQLパズル第2版~プログラミングが変わる書き方/考え方 | Joe Celko, ミック