Problem

Table: Friends

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
| activity      | varchar |
+---------------+---------+
id is the id of the friend and primary key for this table.
name is the name of the friend.
activity is the name of the activity which the friend takes part in.

Table: Activities

1
2
3
4
5
6
7
8

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| name          | varchar |
+---------------+---------+
id is the primary key for this table.
name is the name of the activity.

Write an SQL query to find the names of all the activities with neither maximum, nor minimum number of participants.

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

Examples

Example 1

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

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 |
+------+--------------+

Result table:
+--------------+
| results      |
+--------------+
| Singing      |
+--------------+

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

Solution

Method 1 – Using Aggregation and Subqueries

Intuition

The key idea is to count the number of participants for each activity, then exclude activities with the maximum and minimum participant counts. This works because the problem asks for activities that are neither the most nor the least popular.

Approach

  1. Join the Activities and Friends tables to associate each activity with its participants.
  2. Group by activity name and count the number of participants for each activity.
  3. Find the maximum and minimum participant counts across all activities.
  4. Select activities whose participant count is neither the maximum nor the minimum.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

SELECT activity AS results
FROM (
    SELECT a.name AS activity, COUNT(f.id) AS participant_count
    FROM Activities a
    JOIN Friends f ON a.name = f.activity
    GROUP BY a.name
) t
WHERE participant_count NOT IN (
    (SELECT MAX(cnt) FROM (
        SELECT COUNT(*) AS cnt
        FROM Friends
        GROUP BY activity
    ) x),
    (SELECT MIN(cnt) FROM (
        SELECT COUNT(*) AS cnt
        FROM Friends
        GROUP BY activity
    ) y)
)

Complexity

  • ⏰ Time complexity: O(n) where n is the number of rows in the Friends table.
  • 🧺 Space complexity: O(k) where k is the number of unique activities.