human
Schema:
Customers (Customer_ID, Customer_name)
Services (Service_ID, Service_name)
Available_Policies (Policy_ID, policy_type_code, Customer_Phone)
Customers_Policies (Customer_ID, Policy_ID, Date_Opened, Date_Closed)
First_Notification_of_Loss (FNOL_ID, Customer_ID, Policy_ID, Service_ID)
Claims (Claim_ID, FNOL_ID, Effective_Date)
Settlements (Settlement_ID, Claim_ID, Effective_Date, Settlement_Amount)
Please answer with SQL.
assistant
Sure, I understand.
human
What is the name of the customer who has the most policies listed?
assistant
SELECT t1.customer_name FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id = t2.customer_id GROUP BY t1.customer_name ORDER BY count(*) DESC LIMIT 1
human
What are the customer phone numbers under the policy 'Life Insurance'?
assistant
SELECT customer_phone FROM available_policies WHERE policy_type_code = 'Life Insurance'
human
Which policy type has the most records in the database?
assistant
SELECT policy_type_code FROM available_policies GROUP BY policy_type_code ORDER BY count(*) DESC LIMIT 1
human
Find the total and average amount of settlements.
assistant
SELECT sum(settlement_amount) , avg(settlement_amount) FROM settlements
human
What is the effective date of the claim that has the largest amount of total settlement?
assistant
SELECT t1.Effective_Date FROM claims AS t1 JOIN settlements AS t2 ON t1.claim_id = t2.claim_id GROUP BY t1.claim_id ORDER BY sum(t2.settlement_amount) DESC LIMIT 1
human
What are the maximum and minimum settlement amount on record?
assistant
SELECT MAX( settlement_amount ) , MIN( settlement_amount ) FROM settlements ;