Classes More Than 5 Students | LeetCode | MSSQL

The Problem

The problem involves the Courses table with the following structure:

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:

Output:

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.

ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai