SQL Interview Questions – Activity Participants

Problem Description –

Write an SQL query to 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 query result format is in the following example.

Difficulty Level – Medium

Problem Link – Activity Participants

Solution –

WITH result as (
SELECT
    activity,
    COUNT(*) as total
FROM Friends
GROUP BY 1
)

SELECT
    activity
FROM Friends
GROUP BY 1
HAVING COUNT(*) < (SELECT MAX(total) FROM result)
AND COUNT(*) > (SELECT MIN(total) FROM result)

This is a common SQL interview question where you need to exclude the maximum and minimum value of something. So first you do the aggregation then exclude the min and max value from the result.

We have solved a similar problem before here – SQL Interview Questions – Averages without High or Low Values.