Activity Participants - Practice Coding Problems

Activity Participants - Problem

Database Medium

You're working on a social platform that tracks friend activities! You have two tables: one containing Friends with their favorite activities, and another containing all available Activities.

Your task is to find activities that are neither too popular nor too unpopular - essentially the "middle ground" activities that don't have the maximum or minimum number of participants.

Goal: Return the names of activities that have neither the highest nor the lowest participation count.

Tables:

Friends: Contains friend ID, name, and their chosen activity
Activities: Contains all available activity names

Key Points:

Each friend participates in exactly one activity
Some activities might have zero participants
We want to exclude both the most and least popular activities

Input & Output

example_1.sql — Basic Case

$ Input: Friends: [(1,'Alice','hiking'), (2,'Bob','swimming'), (3,'Charlie','hiking'), (4,'David','cycling'), (5,'Eve','swimming')] Activities: ['hiking','swimming','cycling','reading']

› Output: ['cycling']

💡 Note: hiking has 2 participants (max), reading has 0 participants (min), swimming has 2 participants (max). Only cycling with 1 participant is neither min nor max.

example_2.sql — Multiple Middle Activities

$ Input: Friends: [(1,'Alice','chess'), (2,'Bob','tennis'), (3,'Charlie','chess'), (4,'David','soccer')] Activities: ['chess','tennis','soccer','reading','painting']

› Output: ['tennis','soccer']

💡 Note: chess has 2 participants (max), reading and painting have 0 participants (min). Tennis and soccer each have 1 participant, which is the middle count.

example_3.sql — Edge Case

$ Input: Friends: [(1,'Alice','dancing')] Activities: ['dancing','singing','acting']

› Output: []

💡 Note: dancing has 1 participant (max), singing and acting have 0 participants (min). No activities have counts that are neither min nor max.

Visualization

Tap to expand

Understanding the Visualization

Gather Data

Collect all employees' activity preferences and available activities

Count Participants

Tally how many people signed up for each activity

Find Extremes

Identify the most and least popular activities

Select Moderate

Choose activities with moderate participation for balanced planning

Key Takeaway

🎯 Key Insight: Use window functions to calculate participation statistics and filter activities with moderate engagement levels in a single efficient query.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass with sorting for window functions, optimized by database engine

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results and window function calculations

⚡ Linearithmic Space

Constraints

1 ≤ Friends table rows ≤ 10⁴
1 ≤ Activities table rows ≤ 50
All activity names in Friends table exist in Activities table
Activity names are case-sensitive
Each friend participates in exactly one activity

Asked in

f Meta 35 G Google 28 a Amazon 22 ⊞ Microsoft 18

The optimal solution uses a single SQL query with window functions to efficiently count participants per activity and identify those with moderate participation levels. The key insight is leveraging LEFT JOIN to include activities with zero participants and MIN()/MAX() OVER() to calculate statistics in one pass.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(n)	Use multiple separate subqueries to count, find min/max, then filter
Optimal Single Query (Window Functions)	O(n log n)	O(n)	Single optimized SQL query using window functions and CTEs
Two-Pass with Window Functions	O(n log n)	O(n)	Use window functions to calculate statistics in fewer passes

Brute Force (Multiple Subqueries) — Algorithm Steps

Count participants for each activity using GROUP BY
Find the minimum participant count with a separate subquery
Find the maximum participant count with another subquery
Filter activities that don't have min or max counts

Visualization

Tap to expand

Step-by-Step Walkthrough

First Scan

Count participants for each activity

Second Scan

Find minimum count

Third Scan

Find maximum count

Final Filter

Select activities excluding min/max

Code -

solution.c — C

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

// Simplified C implementation structure
typedef struct {
    char activity[100];
    int count;
} ActivityCount;

void activityParticipantsBruteForce() {
    // Implementation would require complex string handling in C
    printf("Brute force solution structure\n");
    // 1. Create array of activities with counts
    // 2. Multiple passes to find min/max
    // 3. Filter results
}

int main() {
    activityParticipantsBruteForce();
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Multiple scans of the same data - once for counting, once for finding min, once for finding max

⚠ Quadratic Growth

Space Complexity

O(n)

Space for intermediate result sets and temporary tables

⚡ Linearithmic Space

Constraints

1 ≤ Friends table rows ≤ 10⁴
1 ≤ Activities table rows ≤ 50
All activity names in Friends table exist in Activities table
Activity names are case-sensitive
Each friend participates in exactly one activity

23.4K Views

Medium Frequency

~15 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler