Activity Participants Problem


Description

LeetCode Problem 1355.

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.

The query result format is in the following example:

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:
+--------------+
| activity     |
+--------------+
| 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.)


MySQL Solution

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select activity
from friends
group by activity
having count(name) != (
    select max(ma)
    from(
         select activity, count(name) as ma
         from friends
         group by activity
    ) maxAct)
and count(name) != (
    select min(mi)
    from(
         select activity, count(name) as mi
         from friends
         group by activity
    ) minAct)




Related Posts

Get The Second Most Recent Activity Problem

LeetCode 1369. Write an SQL query to show the second...

Movie Rating Problem

LeetCode 1341. Write the following SQL query to find the...

Number Of Trusted Contacts Of A Customer Problem

LeetCode 1364. Write an SQL query to find the following...

Activity Participants Problem

LeetCode 1355. Write an SQL query to find the names...

Students With Invalid Departments Problem

LeetCode 1350. Write an SQL query to find the id...

Replace Employee ID With The Unique Identifier Problem

LeetCode 1378. Write an SQL query to show the unique...