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
Hello there! Can you provide me with a list of all of the service names?
assistant
SELECT service_name FROM services;
human
Thank you very much for that! Could you please filter this list to show only the Service Names which occur more than once on the First Notification of Loss table?
assistant
SELECT t2.service_name FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id = t2.service_id GROUP BY t1.service_id HAVING COUNT( * ) > 1;
human
My apologies! I meant to ask for the list to be filtered to show only the Service Names which occur more than 2 times on the First_Notification_of_Loss table?
assistant
SELECT t2.service_name FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id = t2.service_id GROUP BY t1.service_id HAVING COUNT( * ) > 2;
human
Awesome! Can you show me how many times New policy application has been used?
assistant
SELECT t2.service_name , COUNT( * ) FROM first_notification_of_loss AS t1 JOIN services AS t2 ON t1.service_id = t2.service_id GROUP BY t1.service_id HAVING Service_name = "New policy application" ;