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
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
Of all the claims, what was the earliest date when any claim was made?
assistant
SELECT Date_Claim_Made FROM Claims ORDER BY Date_Claim_Made ASC LIMIT 1
human
Find the total claimed amount of all the claims.
assistant
SELECT sum(Amount_Claimed) FROM Claims
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 claims have a claimed amount larger than the average? List the date the claim was made and the date it was settled.
assistant
SELECT Date_Claim_Made , Date_Claim_Settled FROM Claims WHERE Amount_Claimed > ( SELECT avg(Amount_Claimed) FROM Claims )
human
Return the claim start date for the claims whose claimed amount is no more than the average
assistant
SELECT Date_Claim_Made FROM Claims WHERE Amount_Settled <= ( SELECT avg ( Amount_Settled ) FROM Claims ) ;