Classes With at Least 5 Students - Practice Coding Problems

Classes With at Least 5 Students - Problem

Database Easy

Problem Statement:
You are given a database table containing student enrollment records. Your task is to identify popular classes that have attracted at least 5 students.

Table Structure:
Courses table:

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| student     | varchar |
| class       | varchar |
+-------------+---------+

The combination (student, class) is the primary key, ensuring each student-class pair is unique.

Goal: Write a SQL query to find all classes that have at least 5 students enrolled. Return the result in any order.

Example:
Input table:

+---------+----------+
| student | class    |
+---------+----------+
| A       | Math     |
| B       | English  |
| C       | Math     |
| D       | Biology  |
| E       | Math     |
| F       | Computer |
| G       | Math     |
| H       | Math     |
| I       | Math     |
+---------+----------+

Output:

+---------+
| class   |
+---------+
| Math    |
+---------+

Input & Output

example_1.sql — Basic Example

$ Input: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+----------+

› Output: +---------+ | class | +---------+ | Math | +---------+

💡 Note: Math has 6 students (A, C, E, G, H, I) which is >= 5. English has 1, Biology has 1, and Computer has 1 student each, so they don't meet the threshold.

example_2.sql — Multiple Popular Classes

$ Input: Courses table: +---------+----------+ | student | class | +---------+----------+ | A | Math | | B | English | | C | Math | | D | English | | E | Math | | F | English | | G | Math | | H | English | | I | Math | | J | English | +---------+----------+

💡 Note: Both Math and English have exactly 5 students each, so both classes are returned.

example_3.sql — No Popular Classes

$ Input: Courses table: +---------+---------+ | student | class | +---------+---------+ | A | Math | | B | English | | C | Biology | | D | Physics | +---------+---------+

› Output: +---------+ | class | +---------+ (empty result)

💡 Note: Each class has only 1 student, so no class meets the minimum requirement of 5 students.

Visualization

Tap to expand

Understanding the Visualization

Collect Enrollment Data

Gather all student-class enrollment pairs from the registration system

Group by Class

Organize the data by grouping all students who enrolled in the same class together

Count Students per Class

Count how many students are in each class group

Apply Popularity Threshold

Filter to keep only classes that meet the minimum enrollment requirement (≥5 students)

Key Takeaway

🎯 Key Insight: GROUP BY with HAVING is the perfect SQL pattern for counting occurrences and filtering based on counts - it's efficient, readable, and leverages database engine optimizations.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table to group and count

✓ Linear Growth

Space Complexity

O(k)

Space for k distinct classes in the result set

✓ Linear Space

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ student.length ≤ 20
1 ≤ class.length ≤ 20
student and class consist of letters and numbers only
Each (student, class) pair is unique (primary key constraint)

Asked in

G Google 15 a Amazon 25 f Meta 12 ⊞ Microsoft 18

The optimal solution uses GROUP BY class to group records by class name and HAVING COUNT(*) >= 5 to filter classes with at least 5 students. This approach efficiently counts students per class in a single table scan with O(n) time complexity, making it the standard SQL idiom for counting and filtering problems.

Common Approaches

Approach	Time	Space	Notes
✓ Subquery Approach (Brute Force)	O(n²)	O(n)	For each class, count students using a subquery
GROUP BY with HAVING (Optimal)	O(n)	O(k)	Group by class and count students in a single pass

Subquery Approach (Brute Force) — Algorithm Steps

Get all distinct classes from the table
For each class, use a subquery to count enrolled students
Filter classes where the count >= 5

Visualization

Tap to expand

Step-by-Step Walkthrough

Get Distinct Classes

Extract all unique class names

Count Per Class

For each class, count enrolled students using subquery

Filter Results

Keep only classes with count >= 5

Code -

solution.c — C

// SQL query using subquery approach
char* sqlQuery = "\
SELECT DISTINCT c1.class \
FROM Courses c1 \
WHERE ( \
    SELECT COUNT(*) \
    FROM Courses c2 \
    WHERE c2.class = c1.class \
) >= 5;";

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each distinct class, we scan the entire table to count students

⚠ Quadratic Growth

Space Complexity

O(n)

Space needed for intermediate results and distinct classes

⚡ Linearithmic Space

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ student.length ≤ 20
1 ≤ class.length ≤ 20
student and class consist of letters and numbers only
Each (student, class) pair is unique (primary key constraint)

89.5K Views

High Frequency

~15 min Avg. Time

2.5K 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

Subquery Approach (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler