Skip to main content
Talentd

Activity Participants - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: IBM
Practice this problem

Problem Statement

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.)

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
GROUP BY with Aggregation + SubqueriesO(n)O(k)General SQL solution when you need to filter groups based on global min/max aggregates
GROUP BY with Derived Table (CTE) FilteringO(n)O(k)Preferred for readability when computing counts once and reusing them for min/max comparison

Video Solution

LeetCode Medium 1355 IBM Interview SQL Question with Detailed Explanation • Everyday Data Science • 3,359 views views

Watch 3 more video solutions →

Frequently Asked Questions

Is Activity Participants easy or hard?
Activity Participants is rated Medium difficulty. The challenge is recognizing that you must aggregate participant counts per activity first, then compare those counts against the global minimum and maximum values.
Activity Participants Python/Java solution
This problem is categorized under Database on LeetCode, so the expected solution is written in SQL (typically MySQL). Python or Java are not required because the task is to write a query that aggregates and filters activity participation directly in the database.
How to solve Activity Participants in O(n)?
Group rows by activity using GROUP BY and compute COUNT(*) for each activity. Store the results in a subquery or CTE, then filter rows where the count is not equal to the minimum or maximum participant count. Since the table is scanned once for aggregation, the overall complexity is O(n).
What is the best approach for Activity Participants?
The best approach uses SQL GROUP BY to count participants per activity, then filters out activities with the global minimum and maximum counts. A derived table or CTE stores the aggregated counts, and MIN and MAX are applied to that result. This runs in O(n) time with O(k) space where k is the number of activities.
Is Activity Participants asked at Google/Amazon/Meta?
Database aggregation problems like Activity Participants are common in SQL interviews at large companies including Amazon, Google, and Meta. They test understanding of GROUP BY, aggregate functions, and filtering aggregated results using HAVING or subqueries.
What data structure is used in Activity Participants?
In SQL terms, the solution relies on relational aggregation using GROUP BY rather than traditional data structures. Internally, the database engine may use hash aggregation or sorting to compute counts per activity efficiently.
What is the time complexity of Activity Participants?
The query scans the participation table once to compute counts using GROUP BY. This results in O(n) time complexity where n is the number of rows in the table. The additional MIN and MAX aggregation over grouped results is small, typically O(k) where k is the number of distinct activities.

Ready to solve this problem?

Practice Activity Participants with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary