Activity Participants - Problem

Given two tables Friends and Activities, find the names of all activities with neither the maximum nor the minimum number of participants.

Each activity in the Activities table is performed by any person in the Friends table. Return the result in any order.

  • Count participants for each activity
  • Exclude activities with maximum participant count
  • Exclude activities with minimum participant count
  • Return the middle-range activities

Table Schema

Friends
Column Name Type Description
id PK int Primary key - friend identifier
name varchar Name of the friend
activity varchar Name of activity the friend participates in
Primary Key: id
Activities
Column Name Type Description
id PK int Primary key - activity identifier
name varchar Name of the activity
Primary Key: id

Input & Output

Example 1 — Basic Activity Filtering
Input Tables:
Friends
id name activity
1 Alice Travel
2 Bob Travel
3 Charlie Dancing
4 David Singing
5 Eve Singing
6 Frank Singing
Activities
id name
1 Travel
2 Dancing
3 Singing
Output:
name
Travel
💡 Note:

Travel has 2 participants, Dancing has 1 participant, and Singing has 3 participants. Since Dancing has the minimum (1) and Singing has the maximum (3), only Travel would qualify. However, Travel also ties for minimum with Dancing, so no activities remain after filtering.
Example 2 — Multiple Middle Activities
Input Tables:
Friends
id name activity
1 Alice Travel
2 Bob Dancing
3 Charlie Dancing
4 David Singing
5 Eve Singing
6 Frank Singing
7 Grace Reading
8 Henry Reading
Activities
id name
1 Travel
2 Dancing
3 Singing
4 Reading
Output:
name
Dancing
Reading
💡 Note:

Travel has 1 participant (minimum), Singing has 3 participants (maximum), while Dancing and Reading each have 2 participants. Since 2 is neither the minimum (1) nor maximum (3), both Dancing and Reading are returned.

Constraints

  • 1 ≤ Friends.id ≤ 1000
  • 1 ≤ Activities.id ≤ 1000
  • Friends.name and Activities.name contain only English letters
  • Each activity in Activities table appears at least once in Friends table

Visualization

Tap to expand
Activity Participants - SQL Problem INPUT TABLES Activities id name 1 Singing 2 Reading 3 Swimming Friends id name activity 1 Alice Singing 2 Bob Singing 3 Carol Reading 4 Dan Swimming 5 Eve Swimming 6 Frank Swimming Singing: 2 | Reading: 1 | Swimming: 3 ALGORITHM STEPS 1 Count Participants GROUP BY activity, COUNT(*) Singing --> 2 Reading --> 1 (MIN) Swimming --> 3 (MAX) 2 Find MIN and MAX Subqueries with MIN/MAX MIN = 1 (Reading) MAX = 3 (Swimming) 3 Exclude MIN/MAX WHERE cnt NOT IN (min, max) Reading (1) - EXCLUDED Swimming (3) - EXCLUDED 4 Return Result SELECT activity name Singing (count=2) - OK FINAL RESULT Activities with neither MIN nor MAX Participant Counts Reading: 1 (MIN) Singing: 2 Swimming: 3 (MAX) OUTPUT Singing 1 activity returned Not MIN (1) or MAX (3) Status: OK Key Insight: Use subqueries to find MIN and MAX participant counts, then filter out activities matching those extremes. The HAVING clause compares each activity's count against the MIN/MAX subquery results. SQL Pattern: SELECT name FROM Activities WHERE name IN (subquery with COUNT NOT IN (MIN, MAX)) TutorialsPoint - Activity Participants | Optimal Solution

Related Problems

Asked in
Amazon 15 Google 12 Facebook 8
23.4K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen