Activity Participants

Activity Participants

What is this problem about?

The Activity Participants coding problem asks you to query a database to find the names of activities that have neither the maximum nor the minimum number of participants. You are typically given a Friends table and an Activities table.

Why is this asked in interviews?

This Activity Participants interview question is frequently used by IBM to test a candidate's ability to handle outlier exclusion. In data analysis, you often need to remove the "best" and "worst" performers to see the average behavior, and this SQL problem mimics that real-world scenario perfectly.

Algorithmic pattern used

This problem uses an Aggregation and Subquery interview pattern. You must:

1. Count participants per activity.
2. Identify the global MAX and MIN counts from those results.
3. Filter the activities whose counts do not match either value.

Example explanation

If 'Football' has 10 people, 'Eating' has 2 people, and 'Singing' has 5 people:

• The Max is 10.
• The Min is 2.
• 'Singing' (5) is the only activity that is neither the max nor the min, so it is the correct result.

Common mistakes candidates make

• Hardcoding Values: Assuming there is only one max or one min. Multiple activities can share the maximum or minimum count.
• Using LIMIT: Trying to use ORDER BY and OFFSET which doesn't work if there are ties at the top or bottom.
• Empty Result Sets: Not considering the case where all activities have the same number of participants (in which case the output should be empty because every activity is both the max and the min).

Interview preparation tip

When filtering based on aggregate limits, use IN or NOT IN with subqueries. It makes the logic explicit and handles ties automatically.