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 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
What is the distinct service types that are provided by the organization which has detail 'Denesik and Sons Party'?
assistant
SELECT DISTINCT T1.service_type_code FROM services AS T1 JOIN organizations AS T2 ON T1.organization_id = T2.organization_id WHERE T2.organization_details = 'Denesik AND Sons Party'
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 services has each resident requested? List the resident id, details, and the count in descending order of the count.
assistant
SELECT T1.resident_id , T1.other_details , count(*) FROM Residents AS T1 JOIN Residents_Services AS T2 ON T1.resident_id = T2.resident_id GROUP BY T1.resident_id ORDER BY count(*) DESC
human
Which organizations are not a parent organization of others? List the organization id.
assistant
SELECT organization_id FROM organizations EXCEPT SELECT parent_organization_id FROM organizations
human
List the id and type of each thing, and the details of the organization that owns it.
assistant
SELECT T1.thing_id , T1.type_of_Thing_Code , T2.organization_details FROM Things AS T1 JOIN Organizations AS T2 ON T1.organization_id = T2.organization_id ;