596. Classes More Than 5 Students
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 0
Acceptance rate: 38.9%
Problem Breakdown
The problem asks us to find classes that have more than 5 students. We are given a table named Enrollments with columns student_id, class_id, and class_name. The task is to return the names of classes that meet the criteria.
Inputs:
- A table
Enrollmentswith at least three columns:student_id,class_id, andclass_name.
Outputs:
- A list of class names where each class has more than 5 students.
Implicit Constraints or Tricky Edge Cases:
- The input table might be empty.
- There could be duplicate entries for the same student in a class, which should not be counted multiple times.
- The
class_namemight contain special characters or spaces.
Intuition
The brute-force approach would involve counting students in each class using nested loops, but this would result in high time complexity (O(n^2)). Instead, we can leverage SQL’s aggregation functions to efficiently count the number of unique students per class and filter out those with more than 5 students. The key insight is to use GROUP BY on class_id and then apply a HAVING clause to filter classes based on the student count.
Approach
- Grouping: Use the
GROUP BYclause onclass_idto aggregate records by class. - Counting Unique Students: Within each group, use
COUNT(DISTINCT student_id)to ensure that each student is counted only once per class. - Filtering: Apply a
HAVINGclause to filter out classes where the count of unique students is greater than 5. - Selecting Class Names: Finally, select the
class_namefrom the filtered results.
Complexity Analysis
- Time Complexity: O(n), where n is the number of rows in the
Enrollmentstable. The grouping and counting operations are linear with respect to the input size. - Space Complexity: O(1) for the query execution itself, assuming the database handles memory efficiently. However, the output space could be up to O(m), where m is the number of classes that meet the criteria.
Solution
SELECT class_name
FROM Enrollments
GROUP BY class_id
HAVING COUNT(DISTINCT student_id) > 5;
Edge Cases Handled
- Empty Table: If the
Enrollmentstable is empty, the query will return an empty result set. - Duplicate Entries: The use of
COUNT(DISTINCT student_id)ensures that duplicate entries for the same student in a class are not counted multiple times. - Special Characters/Spaces in Class Names: The query handles class names with special characters or spaces without any issues, as SQL treats them as part of the string.