human
Schema:
Customers (customer_id, customer_details)
Properties (property_id, property_type_code, property_address, other_details)
Residents (resident_id, property_id, date_moved_in, date_moved_out, other_details)
Organizations (organization_id, parent_organization_id, organization_details)
Services (service_id, organization_id, service_type_code, service_details)
Residents_Services (resident_id, service_id, date_moved_in, property_id, date_requested, date_provided, other_details)
Things (thing_id, organization_id, Type_of_Thing_Code, service_type_code, service_details)
Customer_Events (Customer_Event_ID, customer_id, date_moved_in, property_id, resident_id, thing_id)
Customer_Event_Notes (Customer_Event_Note_ID, Customer_Event_ID, service_type_code, resident_id, property_id, date_moved_in)
Timed_Status_of_Things (thing_id, Date_and_Date, Status_of_Thing_Code)
Timed_Locations_of_Things (thing_id, Date_and_Time, Location_Code)
Please answer with SQL.
assistant
Sure, I understand.
human
Which events have the number of notes between one and three? List the event id and the property id.
assistant
SELECT T1.Customer_Event_ID , T1.property_id FROM Customer_Events AS T1 JOIN Customer_Event_Notes AS T2 ON T1.Customer_Event_ID = T2.Customer_Event_ID GROUP BY T1.customer_event_id HAVING count(*) BETWEEN 1 AND 3
human
What is each customer's move in date, and the corresponding customer id and details?
assistant
SELECT T2.date_moved_in , T1.customer_id , T1.customer_details FROM Customers AS T1 JOIN Customer_Events AS T2 ON T1.customer_id = T2.customer_id
human
How many distinct locations have the things with service detail 'Unsatisfied' been located in?
assistant
SELECT count(DISTINCT T2.Location_Code) FROM Things AS T1 JOIN Timed_Locations_of_Things AS T2 ON T1.thing_id = T2.thing_id WHERE T1.service_details = 'Unsatisfied'
human
List the customer event id and the corresponding move in date and property id.
assistant
SELECT customer_event_id , date_moved_in , property_id FROM customer_events
human
What are the id and details of the customers who have at least 3 events?
assistant
SELECT T1.customer_id , T1.customer_details FROM Customers AS T1 JOIN Customer_Events AS T2 ON T1.customer_id = T2.customer_id GROUP BY T1.customer_id HAVING count(*) >= 3
human
How many customers did not have any event?
assistant
SELECT COUNT( * ) FROM customers WHERE customer_id NOT IN ( SELECT customer_id FROM customer_events ) ;