human
Schema:
Customers (Customer_ID, Customer_Details)
Staff (Staff_ID, Staff_Details)
Policies (Policy_ID, Customer_ID, Policy_Type_Code, Start_Date, End_Date)
Claim_Headers (Claim_Header_ID, Claim_Status_Code, Claim_Type_Code, Policy_ID, Date_of_Claim, Date_of_Settlement, Amount_Claimed, Amount_Piad)
Claims_Documents (Claim_ID, Document_Type_Code, Created_by_Staff_ID, Created_Date)
Claims_Processing_Stages (Claim_Stage_ID, Next_Claim_Stage_ID, Claim_Status_Name, Claim_Status_Description)
Claims_Processing (Claim_Processing_ID, Claim_ID, Claim_Outcome_Code, Claim_Stage_ID, Staff_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the description of the claim status 'Open'?
assistant
SELECT claim_status_description FROM claims_processing_stages WHERE claim_status_name = 'Open'
human
What is the name of the customer who has made the minimum amount of payment in one claim?
assistant
SELECT t3.customer_details FROM claim_headers AS t1 JOIN policies AS t2 ON t1.policy_id = t2.policy_id JOIN customers AS t3 ON t2.customer_id = t3.customer_id WHERE t1.amount_piad = (SELECT min(amount_piad) FROM claim_headers)
human
What is the name of the customer who has made the largest amount of claim in a single claim?
assistant
SELECT t3.customer_details FROM claim_headers AS t1 JOIN policies AS t2 ON t1.policy_id = t2.policy_id JOIN customers AS t3 ON t2.customer_id = t3.customer_id WHERE t1.amount_claimed = (SELECT max(amount_claimed) FROM claim_headers)
human
How many distinct claim outcome codes are there?
assistant
SELECT count(DISTINCT claim_outcome_code) FROM claims_processing
human
What is the name of the claim processing stage that most of the claims are on?
assistant
SELECT t2.claim_status_name FROM claims_processing AS t1 JOIN claims_processing_stages AS t2 ON t1.claim_stage_id = t2.claim_stage_id GROUP BY t1.claim_stage_id ORDER BY count(*) DESC LIMIT 1
human
How many claim processing stages are there in total?
assistant
SELECT COUNT( * ) FROM claims_processing_stages ;