Description

Table: Courses

Column NameType
studentvarchar
classvarchar
  • (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.

Problem Statement

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.

Example 1:

Input:

  • Courses table:
studentclass
AMath
BEnglish
CMath
DBiology
EMath
FComputer
GMath
HMath
IMath

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.

Solution

The problem is asking to find classes with at least 5 students. This means find the classes where the count of distinct students is greater than or equal to 5.

  1. Find the count of distinct students in each class. This can be done using aggregation with COUNT(DISTINCT student) function.
1SELECT class, COUNT(DISTINCT student) AS num_students
2    FROM Courses 
3    GROUP BY class;
  1. Filter the classes where the count of distinct students is greater than or equal to 5. This can be done using WHERE clause.
1WITH tmp AS (
2    SELECT class, count(1) AS count 
3        FROM Courses 
4        GROUP BY class 
5) SELECT class 
6    FROM tmp 
7    WHERE count >= 5;

In this solution, I’ve used temporary view. You could also get same result by using HAVING clause to filter by COUNT(DISTINCT student) column.

1SELECT class
2    FROM Courses
3    GROUP BY class
4    HAVING COUNT(DISTINCT student) >= 5;