## DEV Community # Classes More Than 5 Students | LeetCode | MSSQL

## 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.

## 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