human
Schema:
Customers (Customer_ID, Customer_Details)
Customer_Policies (Policy_ID, Customer_ID, Policy_Type_Code, Start_Date, End_Date)
Claims (Claim_ID, Policy_ID, Date_Claim_Made, Date_Claim_Settled, Amount_Claimed, Amount_Settled)
Settlements (Settlement_ID, Claim_ID, Date_Claim_Made, Date_Claim_Settled, Amount_Claimed, Amount_Settled, Customer_Policy_ID)
Payments (Payment_ID, Settlement_ID, Payment_Method_Code, Date_Payment_Made, Amount_Payment)
Please answer with SQL.
assistant
Sure, I understand.
human
List the date the claim was made, the date it was settled and the amount settled for all the claims which had exactly one settlement.
assistant
SELECT T1.claim_id , T1.date_claim_made , T1.Date_Claim_Settled FROM Claims AS T1 JOIN Settlements AS T2 ON T1.Claim_id = T2.Claim_id GROUP BY T1.claim_id HAVING count(*) = 1
human
Among all the claims, which settlements have a claimed amount that is no more than the average? List the claim start date.
assistant
SELECT Date_Claim_Made FROM Claims WHERE Amount_Settled <= ( SELECT avg(Amount_Settled) FROM Claims )
human
How many settlements does each claim correspond to? List the claim id and the number of settlements.
assistant
SELECT T1.Claim_id , count(*) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id = T2.claim_id GROUP BY T1.claim_id
human
Which claims caused more than 2 settlements or have the maximum claim value? List the date the claim was made and the claim id.
assistant
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 )
human
How many settlements were made on the claim with the most recent claim settlement date? List the number and the claim id.
assistant
SELECT count(*) , T1.claim_id FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id = T2.claim_id GROUP BY T1.claim_id ORDER BY T1.Date_Claim_Settled DESC LIMIT 1
human
Which claim incurred the most number of settlements? List the claim id, the date the claim was made, and the number.
assistant
SELECT T1.claim_id , T1.date_claim_made , COUNT( * ) FROM Claims AS T1 JOIN Settlements AS T2 ON T1.claim_id = T2.claim_id GROUP BY T1.claim_id ORDER BY COUNT( * ) DESC LIMIT 1 ;