
CREATE TABLE "Claims" (
	"Claim_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	PRIMARY KEY ("Claim_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Customer_Policies" ("Policy_ID")
)


CREATE TABLE "Customer_Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_Type_Code" CHAR(15) NOT NULL, 
	"Start_Date" DATE, 
	"End_Date" DATE, 
	PRIMARY KEY ("Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID")
)


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Customer_ID")
)


CREATE TABLE "Payments" (
	"Payment_ID" INTEGER NOT NULL, 
	"Settlement_ID" INTEGER NOT NULL, 
	"Payment_Method_Code" VARCHAR(255), 
	"Date_Payment_Made" DATE, 
	"Amount_Payment" INTEGER, 
	PRIMARY KEY ("Payment_ID"), 
	FOREIGN KEY("Settlement_ID") REFERENCES "Settlements" ("Settlement_ID")
)


CREATE TABLE "Settlements" (
	"Settlement_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	"Customer_Policy_ID" INTEGER NOT NULL, 
	PRIMARY KEY ("Settlement_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claims" ("Claim_ID")
)

CREATE TABLE "Claims" (
	"Claim_ID" INTEGER NOT NULL, 
	"Policy_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	PRIMARY KEY ("Claim_ID"), 
	FOREIGN KEY("Policy_ID") REFERENCES "Customer_Policies" ("Policy_ID")
)

/*
2 rows from Claims table:
Claim_ID	Policy_ID	Date_Claim_Made	Date_Claim_Settled	Amount_Claimed	Amount_Settled
143	744	2017-03-11	2017-11-03	43884	1085
423	552	2016-08-12	2018-01-27	79134	1724
*/


CREATE TABLE "Customer_Policies" (
	"Policy_ID" INTEGER NOT NULL, 
	"Customer_ID" INTEGER NOT NULL, 
	"Policy_Type_Code" CHAR(15) NOT NULL, 
	"Start_Date" DATE, 
	"End_Date" DATE, 
	PRIMARY KEY ("Policy_ID"), 
	FOREIGN KEY("Customer_ID") REFERENCES "Customers" ("Customer_ID")
)

/*
2 rows from Customer_Policies table:
Policy_ID	Customer_ID	Policy_Type_Code	Start_Date	End_Date
119	1	Car	2018-01-21	2017-12-15
141	2	Life	2017-08-21	2017-09-29
*/


CREATE TABLE "Customers" (
	"Customer_ID" INTEGER NOT NULL, 
	"Customer_Details" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Customer_ID")
)

/*
2 rows from Customers table:
Customer_ID	Customer_Details
1	America Jaskolski
2	Ellsworth Paucek
*/


CREATE TABLE "Payments" (
	"Payment_ID" INTEGER NOT NULL, 
	"Settlement_ID" INTEGER NOT NULL, 
	"Payment_Method_Code" VARCHAR(255), 
	"Date_Payment_Made" DATE, 
	"Amount_Payment" INTEGER, 
	PRIMARY KEY ("Payment_ID"), 
	FOREIGN KEY("Settlement_ID") REFERENCES "Settlements" ("Settlement_ID")
)

/*
2 rows from Payments table:
Payment_ID	Settlement_ID	Payment_Method_Code	Date_Payment_Made	Amount_Payment
384	516	MasterCard	2018-02-16	241730
435	476	MasterCard	2017-05-28	448613
*/


CREATE TABLE "Settlements" (
	"Settlement_ID" INTEGER NOT NULL, 
	"Claim_ID" INTEGER NOT NULL, 
	"Date_Claim_Made" DATE, 
	"Date_Claim_Settled" DATE, 
	"Amount_Claimed" INTEGER, 
	"Amount_Settled" INTEGER, 
	"Customer_Policy_ID" INTEGER NOT NULL, 
	PRIMARY KEY ("Settlement_ID"), 
	FOREIGN KEY("Claim_ID") REFERENCES "Claims" ("Claim_ID")
)

/*
2 rows from Settlements table:
Settlement_ID	Claim_ID	Date_Claim_Made	Date_Claim_Settled	Amount_Claimed	Amount_Settled	Customer_Policy_ID
357	486	2018-08-07	2018-01-16	38543	1181	515
412	621	2017-08-27	2018-02-04	57669	1427	617
*/
Find the claims that led to more than two settlements or have the maximum claim value. For each of them, return the date the claim was made and the id of the claim.
SELECT T1.Date_Claim_Made ,  T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id GROUP BY T1.Claim_id HAVING count(*)  >  2 UNION SELECT T1.Date_Claim_Made ,  T1.Claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id  =  T2.Claim_id WHERE T1.Amount_Claimed  =  ( SELECT max(Amount_Claimed) FROM Claims )
[{'Date_Claim_Made': '2017-08-03', 'Claim_ID': 571}, {'Date_Claim_Made': '2017-10-21', 'Claim_ID': 801}]