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
What is the most popular payment method?
assistant
SELECT Payment_Method_Code FROM Payments GROUP BY Payment_Method_Code ORDER BY count(*) DESC LIMIT 1
human
What are all the distinct details of the customers?
assistant
SELECT DISTINCT customer_details FROM Customers
human
Which customer had at least 2 policies but did not file any claims? List the customer details and id.
assistant
SELECT T1.customer_details , T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id = T2.customer_id GROUP BY T1.customer_id HAVING count(*) >= 2 EXCEPT SELECT T1.customer_details , T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id = T2.customer_id JOIN Claims AS T3 ON T2.policy_id = T3.policy_id
human
List the details of the customers who do not have any policies.
assistant
SELECT customer_details FROM Customers EXCEPT SELECT T1.customer_details FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.customer_id = T2.customer_id
human
Who are the customers that had more than 1 policy? List the customer details and id.
assistant
SELECT T1.customer_details , T1.customer_id FROM Customers AS T1 JOIN Customer_Policies AS T2 ON T1.Customer_id = T2.Customer_id GROUP BY T1.customer_id HAVING count(*) > 1
human
Find the policy type the most customers choose.
assistant
SELECT Policy_Type_Code FROM Customer_Policies GROUP BY Policy_Type_Code ORDER BY COUNT( * ) DESC LIMIT 1 ;