The Problem
The problem involves the Courses
table with the following structure:
student (PK) | class (PK) |
---|---|
varchar | varchar |
Each row of this table indicates the name of a student and the class in which they are enrolled. We are asked to write an SQL query to report all the classes that have at least five students.
Explanation
Here's an example for better understanding:
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 |
Only Math class has at least 5 students.
The Solution
We will explore three different SQL solutions to this problem, examining their differences, strengths, and weaknesses.
Source Code 1
The first solution uses a straightforward GROUP BY
clause to count the number of students per class, then the HAVING
clause to filter the classes with at least 5 students.
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5
This solution has a runtime of 960ms, outperforming 86.50% of other submissions.
Source Code 2
The second solution employs a Window Function COUNT
with OVER
clause. This calculates the count of students in each class, creates a new column "counter" and then, selects the classes where "counter" is at least 5.
WITH class_count AS (
SELECT DISTINCT
class,
COUNT(student) OVER (PARTITION BY class) [counter]
FROM Courses
)
SELECT class
FROM class_count
WHERE counter >= 5
This solution has a runtime of 1022ms, beating 74.27% of other submissions.
Source Code 3
The third solution is quite similar to the second one. The only difference is it uses a subquery instead of a Common Table Expression (CTE).
SELECT c.class
FROM
(
SELECT DISTINCT
class,
COUNT(student) OVER (PARTITION BY class) [counter]
FROM Courses
) [c]
WHERE c.counter >= 5
This solution has a runtime of 1151ms, beating 49.46% of other submissions.
Conclusion
All three solutions provide the correct output but with slight performance differences. Based on the runtimes, we can rank the solutions from best to worst in terms of performance: Source Code 1 > Source Code 2 > Source Code 3.
You can find the original problem at LeetCode.
For more insightful solutions and tech-related content, connect with me on my Beacons page.
Top comments (0)