Description

Table: Friends

Column NameType
idint
namevarchar
activityvarchar
  • 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 NameType
idint
namevarchar
  • In SQL, id is the primary key for this table.
  • name is the name of the activity.

Problem Statement

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:
idnameactivity
1Jonathan D.Eating
2Jade W.Singing
3Victor J.Singing
4Elvis Q.Eating
5Daniel A.Eating
6Bob B.Horse Riding
  • Activities table:
idname
1Eating
2Singing
3Horse 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.)

Solution

The problem is asking to find the names of activities which are done by not maximum number of participants nor by minimum number of participants. The problem can be divided as follows.

  1. Find the number of participants per activity.

This can be done using grouping operation on the activity column and calcuating the number of ids in Friends table.

1SELECT activity, COUNT(1) AS particpants_count
2    FROM Friends
3    GROUP BY activity;
activityparticipants_count
Eating3
Singing2
Horse Riding1
  1. Next, you need to filter out the rows which are maximum number of participants and minimum number of participants.

This two rows can be found using MAX() and MIN() operation on the participants_count from above query.

1SELECT MAX(participant_counts) FROM activity_participants
2    UNION
3    SELECT MIN(participant_counts) FROM activity_participants;

You need filter out these rows using NOT IN clause. So, the final answer is below query.

 1WITH activity_participants AS (
 2    SELECT activity, COUNT(1) AS participant_counts
 3    FROM Friends
 4    GROUP BY activity
 5) SELECT activity 
 6    FROM activity_participants
 7    WHERE participant_counts NOT IN(
 8        SELECT MAX(participant_counts) FROM activity_participants
 9        UNION
10        SELECT MIN(participant_counts) FROM activity_participants
11    );