human
Schema:
Services (Service_ID, Service_Type_Code)
Participants (Participant_ID, Participant_Type_Code, Participant_Details)
Events (Event_ID, Service_ID, Event_Details)
Participants_in_Events (Event_ID, Participant_ID)
Please answer with SQL.
assistant
Sure, I understand.
human
What are all the distinct participant ids who attended any events?
assistant
SELECT count(DISTINCT participant_id) FROM participants_in_Events
human
Which service id and type has the least number of participants?
assistant
SELECT T3.service_id , T4.Service_Type_Code FROM participants AS T1 JOIN Participants_in_Events AS T2 ON T1.Participant_ID = T2.Participant_ID JOIN EVENTS AS T3 ON T2.Event_ID = T3.Event_ID JOIN services AS T4 ON T3.service_id = T4.service_id GROUP BY T3.service_id ORDER BY count(*) ASC LIMIT 1
human
What are the ids and details of events that have more than one participants?
assistant
SELECT T1.event_id , T1.event_details FROM EVENTS AS T1 JOIN Participants_in_Events AS T2 ON T1.Event_ID = T2.Event_ID GROUP BY T1.Event_ID HAVING count(*) > 1
human
What is the id of the event with the most participants?
assistant
SELECT Event_ID FROM Participants_in_Events GROUP BY Event_ID ORDER BY count(*) DESC LIMIT 1
human
How many participants belong to the type 'Organizer'?
assistant
SELECT count(*) FROM participants WHERE participant_type_code = 'Organizer'
human
What is the most common participant type?
assistant
SELECT participant_type_code FROM participants GROUP BY participant_type_code ORDER BY COUNT( * ) DESC LIMIT 1 ;