Table: Courses
+-------------+---------+ | Column Name | Type | +-------------+---------+ | student | varchar | | class | varchar | +-------------+---------+ (student, class) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the name of a student and the class in which they are enrolled.
Write a solution to find all the classes that have at least five students.
Return the result table in any order.
The result format is in the following example.
Example 1:
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 | +---------+ Explanation: - Math has 6 students, so we include it. - English has 1 student, so we do not include it. - Biology has 1 student, so we do not include it. - Computer has 1 student, so we do not include it.
Problem Overview: You are given a table that records which student belongs to which class. The task is to return all class names that have at least five students enrolled. The result should contain only the class identifier.
Approach 1: Using SQL Aggregation Functions (O(n) time, O(k) space)
This is a classic SQL aggregation problem. You group rows by the class column and count how many students appear in each group. The key operation is GROUP BY class, which forms a bucket of rows for every class. Then apply a HAVING COUNT(student) >= 5 filter to keep only classes with at least five students. The database engine performs a single pass through the rows, builds aggregated counts per class, and filters the results afterward.
The time complexity is O(n) where n is the number of records in the table. The database maintains counts for each unique class, which requires O(k) space where k is the number of distinct classes. This is the most natural and expected solution when working directly with relational data.
Approach 2: HashMap Simulation in Iterative Languages (O(n) time, O(k) space)
If the data is processed in an application layer (for example in Python or Java instead of pure SQL), you can simulate the same behavior using a HashMap. Iterate through each record and update a counter for the corresponding class. Each insertion or update in the hash map takes average O(1) time.
After counting all students, iterate over the map entries and collect the classes whose counts are greater than or equal to five. This mirrors the behavior of SQL's GROUP BY + HAVING pipeline. The overall time complexity remains O(n) because each record is processed once, and the space complexity is O(k) for storing counts of distinct classes. This approach is useful when solving the problem outside a database engine or during interviews that expect algorithmic implementations.
Recommended for interviews: Interviewers typically expect the SQL aggregation solution because the problem is tagged as a database query task. Writing GROUP BY with a HAVING COUNT filter shows strong understanding of relational querying. The HashMap approach demonstrates that you understand the underlying mechanics of aggregation, which can help when translating database logic into general programming languages.
The simplest solution to this problem is to exploit SQL's GROUP BY feature alongside the COUNT() function. We'll group students by their class, count the number of students in each group, and then filter those results to include only classes with five or more students.
This SQL query begins with selecting the class from the Courses table. Then, we group the entries by class and use the COUNT() function to count the number of students associated with each class. Finally, we use HAVING to filter down to only those classes where the student count is at least five.
SQL
Time Complexity: O(n), where n is the number of rows in the table, because we need to check each row to perform the GROUP BY operation.
Space Complexity: O(k), where k is the number of unique classes in the output.
In languages like C, C++, Java, Python, C#, and JavaScript, we can simulate the SQL behavior with a hashmap (dictionary in Python, or an object in JavaScript). We iterate through the data, count the occurrences of each class, and then extract the classes that have counts of five or more.
We use a defaultdict from Python's collections module to store each class's student count. As we iterate through the list of tuples courses, we increment the count for the class each student is enrolled in. Finally, we build a result list of classes that have a count of at least five.
Time Complexity: O(n), where n is the number of entries in the courses list, as we iterate through all entries once.
Space Complexity: O(k), the number of distinct classes, because of the hashmap.
We can use the GROUP BY statement to group by class and then use the HAVING statement to filter out the classes with a student count greater than or equal to 5.
MySQL
| Approach | Complexity |
|---|---|
| Using SQL Aggregation Functions | Time Complexity: O(n), where n is the number of rows in the table, because we need to check each row to perform the |
| Using a HashMap to Simulate SQL-Like Behavior in Iterative Languages | Time Complexity: O(n), where n is the number of entries in the courses list, as we iterate through all entries once. |
| Grouping and Aggregation | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Aggregation (GROUP BY + HAVING) | O(n) | O(k) | Best when solving directly in SQL or database interview questions |
| HashMap Counting Simulation | O(n) | O(k) | Useful when implementing the logic in Python, Java, or other iterative languages |
LeetCode 596: Classes More Than 5 Students [SQL] • Frederik Müller • 6,425 views views
Watch 9 more video solutions →Practice Classes More Than 5 Students with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor