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
How many different status codes of things are there?
assistant
SELECT count(DISTINCT Status_of_Thing_Code) FROM Timed_Status_of_Things
human
How many residents does each property have? List property id and resident count.
assistant
SELECT T1.property_id , count(*) FROM properties AS T1 JOIN residents AS T2 ON T1.property_id = T2.property_id GROUP BY T1.property_id
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 )
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
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 ;