LeetCode 596: Classes With at Least 5 Students

Problem Restatement

We are given a table called Courses.

Each row shows that one student is enrolled in one class.

We need to return all classes that have at least five students. The result may be returned in any order. The official table has two columns: student and class.

Table

Courses

The pair (student, class) is the primary key, so the same student-class pair appears only once.

Example

Input:

Output:

Math has six students: A, C, E, G, H, and I.

Every other class has fewer than five students.

First Thought: Count All Rows

A first query might be:

SELECT COUNT(*)
FROM Courses;

This only counts all enrollments in the table.

But the problem asks for classes with at least five students, so we need to count students separately for each class.

Key Insight

We need one group per class.

That means we should use:

GROUP BY class

After grouping, each group contains all rows for one class.

Then we use:

HAVING COUNT(*) >= 5

HAVING is used because the filter depends on an aggregate count. WHERE filters individual rows before grouping, while HAVING filters groups after aggregation.

Algorithm

1. Read rows from Courses.
2. Group rows by class.
3. Count rows in each class group.
4. Keep only groups with count at least 5.
5. Return the class column.

Correctness

The query groups rows by class, so each group contains exactly the students enrolled in one class.

For each class group, COUNT(*) returns the number of student-class rows in that class. Since (student, class) is unique, this is exactly the number of students enrolled in the class.

The HAVING COUNT(*) >= 5 condition keeps exactly the classes with at least five students and excludes all classes with fewer than five students.

Therefore, the query returns exactly the classes required by the problem.

Complexity

Let:

n = number of rows in Courses
c = number of distinct classes

The exact execution plan depends on the SQL engine.

Implementation

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;

Code Explanation

This selects the required output column:

SELECT class

This reads enrollment rows:

FROM Courses

This creates one group per class:

GROUP BY class

This keeps only classes with at least five rows:

HAVING COUNT(*) >= 5

Because each row represents one student enrolled in one class, the count is the number of students in that class.

Testing

Sample data:

CREATE TABLE Courses (
    student VARCHAR(255),
    class VARCHAR(255),
    PRIMARY KEY (student, class)
);

INSERT INTO Courses (student, class) VALUES
('A', 'Math'),
('B', 'English'),
('C', 'Math'),
('D', 'Biology'),
('E', 'Math'),
('F', 'Computer'),
('G', 'Math'),
('H', 'Math'),
('I', 'Math');

Query:

SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(*) >= 5;

Expected result:

Additional test cases: