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