The problem involves the
Courses table with the following structure:
|student (PK)||class (PK)|
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.
Here's an example for better understanding:
Only Math class has at least 5 students.
We will explore three different SQL solutions to this problem, examining their differences, strengths, and weaknesses.
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.
The second solution employs a Window Function
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.
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.
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.