Mid-Band Activity Participation Report

Medium

A community platform tracks member participation across interest activities and wants to highlight activities that are neither least popular nor most popular. Table: friends id: unique member identifier name: member display name activity: activity joined by that member Table: activities id: unique activity identifier name: catalog name of the activity Data guarantees: Every row in friends belongs to exactly one activity. Every activity listed in activities appears at least once in friends. Task: Return activity names whose participant count is strictly greater than the global minimum activity participant count and strictly smaller than the global maximum activity participant count. Output requirements: Return exactly one column named activity. Return each qualifying activity once. Result rows may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

friends:
| id | name       | activity     |
|----|------------|--------------|
| 1  | Jordan     | Dining       |
| 2  | Avery      | Choir        |
| 3  | Mika       | Choir        |
| 4  | Riley      | Dining       |
| 5  | Sam        | Dining       |
| 6  | Harper     | Horseback    |

activities:
| id | name      |
|----|-----------|
| 1  | Dining    |
| 2  | Choir     |
| 3  | Horseback |

Output

[
  {"activity":"Choir"}
]

Explanation

Dining has 3 participants (maximum), Horseback has 1 (minimum), and Choir has 2, so only Choir is in the strict middle range.

Example 2

Input

friends:
| id | name  | activity |
|----|-------|----------|
| 10 | A     | Yoga     |
| 11 | B     | Yoga     |
| 12 | C     | Chess    |
| 13 | D     | Chess    |

activities:
| id | name  |
|----|-------|
| 1  | Yoga  |
| 2  | Chess |

Output

[]

Explanation

Both activities have the same participant count, so minimum and maximum are equal. No activity can be strictly between them.

Example 3

Input

friends:
| id | name   | activity |
|----|--------|----------|
| 21 | N1     | Run      |
| 22 | N2     | Run      |
| 23 | N3     | Run      |
| 24 | N4     | Swim     |
| 25 | N5     | Swim     |
| 26 | N6     | Cycle    |

activities:
| id | name  |
|----|-------|
| 1  | Run   |
| 2  | Swim  |
| 3  | Cycle |

Output

[
  {"activity":"Swim"}
]

Explanation

Participant counts are Run=3, Swim=2, Cycle=1. Only Swim is strictly greater than 1 and strictly less than 3.

Constraints

1 <= number of rows in friends <= 200000
1 <= number of rows in activities <= 200000
1 <= friends.id, activities.id <= 1000000000
friends.id is unique
activities.id is unique
friends.activity values belong to the activities.name catalog
Every activity in activities has at least one participant in friends
Return exactly one column named activity
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with exactly one column named activity

friends: | id | name | activity | |----|------------|--------------| | 1 | Jordan | Dining | | 2 | Avery | Choir | | 3 | Mika | Choir | | 4 | Riley | Dining | | 5 | Sam | Dining | | 6 | Harper | Horseback | activities: | id | name | |----|-----------| | 1 | Dining | | 2 | Choir | | 3 | Horseback |

friends: | id | name | activity | |----|-------|----------| | 10 | A | Yoga | | 11 | B | Yoga | | 12 | C | Chess | | 13 | D | Chess | activities: | id | name | |----|-------| | 1 | Yoga | | 2 | Chess |

friends: | id | name | activity | |----|--------|----------| | 21 | N1 | Run | | 22 | N2 | Run | | 23 | N3 | Run | | 24 | N4 | Swim | | 25 | N5 | Swim | | 26 | N6 | Cycle | activities: | id | name | |----|-------| | 1 | Run | | 2 | Swim | | 3 | Cycle |