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 are all the policy types of the customer that has the most policies listed?
assistant
SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id = t2.customer_id JOIN available_policies AS t3 ON t2.policy_id = t3.policy_id WHERE t1.customer_name = (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
Find the names of customers who have used either the service 'Close a policy' or the service 'Upgrade a policy'.
assistant
SELECT t1.customer_name FROM customers AS t1 JOIN first_notification_of_loss AS t2 ON t1.customer_id = t2.customer_id JOIN services AS t3 ON t2.service_id = t3.service_id WHERE t3.service_name = 'Close a policy' OR t3.service_name = 'Upgrade a policy'
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
Find the policy type used by more than 4 customers.
assistant
SELECT policy_type_code FROM available_policies GROUP BY policy_type_code HAVING count(*) > 4
human
What are all the policy types of the customer named 'Dayana Robel'?
assistant
SELECT DISTINCT t3.policy_type_code FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id = t2.customer_id JOIN available_policies AS t3 ON t2.policy_id = t3.policy_id WHERE t1.customer_name = 'Dayana Robel'
human
Count the total number of policies used by the customer named 'Dayana Robel'.
assistant
SELECT COUNT( * ) FROM customers AS t1 JOIN customers_policies AS t2 ON t1.customer_id = t2.customer_id WHERE t1.customer_name = "Dayana Robel" ;