Skip to content

LeetCode 596 - Classes More Than 5 Students

Database Language: PostgreSQL

Difficulty: ⭐

Problem Description

Input

Table: Courses

Column Name Type
student varchar
class varchar

(student, class) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the name of a student and the class in which they are enrolled.

Requirement

Write a solution to find all the classes that have at least five students.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1

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
Explanation
  • Math has 6 students, so we include it.
  • English has 1 student, so we do not include it.
  • Biology has 1 student, so we do not include it.
  • Computer has 1 student, so we do not include it.

SQL Schema

CREATE TABLE IF NOT EXISTS Courses (student varchar(255), class varchar(255));
ALTER TABLE Courses ADD CONSTRAINT PK_Courses PRIMARY KEY (student, class);

TRUNCATE TABLE Courses;
INSERT INTO Courses (student, class) values ('A', 'Math');
INSERT INTO Courses (student, class) values ('B', 'English');
INSERT INTO Courses (student, class) values ('C', 'Math');
INSERT INTO Courses (student, class) values ('D', 'Biology');
INSERT INTO Courses (student, class) values ('E', 'Math');
INSERT INTO Courses (student, class) values ('F', 'Computer');
INSERT INTO Courses (student, class) values ('G', 'Math');
INSERT INTO Courses (student, class) values ('H', 'Math');
INSERT INTO Courses (student, class) values ('I', 'Math');

Solutions

To be able to find all the classes that have at least five students, the number of students per class needs to be determined first. To cound the number of students per class, the COUNT() aggregation function will be used together with the GROUP BY clause of the SELECT statement:

SELECT class, COUNT(student) as student_count
FROM Courses
GROUP BY class
class student_count
Math 6
English 1
Biology 1
Computer 1

To return just the list of classes that have at least five student, the result above need to be filtered using the HAVING clause:

SELECT class, COUNT(student) as student_count
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5
class student_count
Math 6

As can be seen from this query, the HAVING clause used the aggregate function COUNT(student) instead of the student_count column alias. In other databases such as MySQL and SQL Server, you can reference the column alias in the HAVING clause, but in PostgreSQL, you will get an error:

SELECT class, COUNT(student) as student_count
FROM Courses
GROUP BY class
HAVING student_count >= 5

ERROR:  column "student_count" does not exist
LINE 4: HAVING student_count >= 5;
               ^ 

SQL state: 42703
Character: 82

The required output for this question is just the class and not include the student_count so this column needs to be removed and the resulting query is answer to the question:

# Final Solution Query
SELECT class
FROM Courses
GROUP BY class
HAVING COUNT(student) >= 5

Here's the query plan generated by PostgreSQL for this query:

HashAggregate  (cost=11.05..11.93 rows=23 width=516)
  Group Key: class
  Filter: (count(student) >= 5)
  ->  Seq Scan on courses  (cost=0.00..10.70 rows=70 width=1032)

And here's the fastest runtime:

  • Runtime: 272ms
  • Beats: 91.06% as of July 28, 2024