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
Count the number of participants for each event.
assistant
SELECT event_id , COUNT( * ) FROM Participants_in_Events GROUP by event_id;
human
Among those, which event id has the least participants?
assistant
SELECT event_id FROM Participants_in_Events GROUP by event_id ORDER by COUNT( * ) asc LIMIT 1;
human
What service id is it?
assistant
SELECT service_id FROM Events WHERE event_id = ( SELECT event_id FROM Participants_in_Events GROUP by event_id ORDER by COUNT( * ) asc LIMIT 1 );
human
Also list the service type of it.
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 ;