Watch 4 video solutions for Activity Participants, a medium level problem involving Database. This walkthrough by Everyday Data Science has 3,230 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Friends
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | | activity | varchar | +---------------+---------+ id is the id of the friend and the primary key for this table in SQL. name is the name of the friend. activity is the name of the activity which the friend takes part in.
Table: Activities
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | name | varchar | +---------------+---------+ In SQL, id is the primary key for this table. name is the name of the activity.
Find the names of all the activities with neither the maximum nor the minimum number of participants.
Each activity in the Activities table is performed by any person in the table Friends.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Friends table: +------+--------------+---------------+ | id | name | activity | +------+--------------+---------------+ | 1 | Jonathan D. | Eating | | 2 | Jade W. | Singing | | 3 | Victor J. | Singing | | 4 | Elvis Q. | Eating | | 5 | Daniel A. | Eating | | 6 | Bob B. | Horse Riding | +------+--------------+---------------+ Activities table: +------+--------------+ | id | name | +------+--------------+ | 1 | Eating | | 2 | Singing | | 3 | Horse Riding | +------+--------------+ Output: +--------------+ | activity | +--------------+ | Singing | +--------------+ Explanation: Eating activity is performed by 3 friends, maximum number of participants, (Jonathan D. , Elvis Q. and Daniel A.) Horse Riding activity is performed by 1 friend, minimum number of participants, (Bob B.) Singing is performed by 2 friends (Victor J. and Jade W.)
Problem Overview: You are given a table that records which user participated in which activity. The task is to return the activities whose participant count is neither the maximum nor the minimum among all activities. In other words, filter out activities with the highest and lowest participation and keep the rest.
Approach 1: GROUP BY with Aggregation and Subqueries (O(n) time, O(k) space)
Start by grouping rows by activity and computing the number of participants using COUNT(*). This produces a participant count for each activity. Once you have those counts, compute the global MIN and MAX of these values using subqueries. Finally, filter activities whose counts are not equal to either extreme. The key idea is that aggregation reduces the dataset to one row per activity, making it easy to compare counts across activities.
This approach works well in SQL because databases optimize GROUP BY and aggregate functions efficiently. The query performs a single table scan to build counts and a small aggregation over the grouped results. Time complexity is O(n) where n is the number of participation rows, and space complexity is O(k) where k is the number of distinct activities.
Implementation typically uses a derived table or common table expression to store activity counts, then filters rows with NOT IN (min_count, max_count). This pattern appears frequently in database interview problems involving ranking or filtering aggregated results.
Recommended for interviews: The GROUP BY aggregation approach with subqueries is the expected solution. It shows you understand SQL aggregation, derived tables, and how to compare group-level metrics like MIN and MAX. Interviewers usually want to see clean use of aggregation rather than procedural logic. Mastering this pattern helps with many SQL and database analytics queries.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| GROUP BY with Aggregation + Subqueries | O(n) | O(k) | General SQL solution when you need to filter groups based on global min/max aggregates |
| GROUP BY with Derived Table (CTE) Filtering | O(n) | O(k) | Preferred for readability when computing counts once and reusing them for min/max comparison |
