Classes More Than 5 Students | Leetcode 596 | Crack SQL Interviews in 50 Qs

Welcome to Question 26 of the "Crack SQL Interview in 50 Questions" series. This chapter covers a classic aggregation and grouping problem from LeetCode (Problem 596), focusing on how to filter summarized group results using SQL clauses like GROUP BY and HAVING.

1. Problem Statement & Schema Overview

We are given a table named courses that tracks student enrollments across various academic classes.

Table Schema: courses

• student: Data type VARCHAR (Represents the name or ID of the student).
• class: Data type VARCHAR (Represents the name of the class).
• Primary Key: (student, class) — This composite primary key guarantees that each student-class enrollment pair is unique (i.e., a student cannot enroll in the exact same class twice).

Objective

Write a SQL query to find all classes that have at least 5 students enrolled in them. The result table can be returned in any order.

2. Real-Life Analogy: School Attendance Register

Imagine walking into a school administration office where a giant ledger book lists every student alongside every class they take.

To find out which classes are running popular sessions (classes with $\ge 5$ students), you wouldn't look at individual rows one by one and make a final decision immediately. Instead, you would:

1. Group all the rows by the class name (gather all Math students in one pile, English students in another).
2. Count how many student slips are in each class pile.
3. Filter out any class piles that have fewer than 5 slips, keeping only the large classes.

3. Step-by-Step Example Walkthrough

Let's examine a sample input dataset to see how rows map to class counts.

Breakdown by Class:

• Math: Students A, C, E, G, H, I $\rightarrow$ Total Count = 6
• English: Student B $\rightarrow$ Total Count = 1
• Biology: Student D $\rightarrow$ Total Count = 1
• Computer: Student F $\rightarrow$ Total Count = 1

Since the problem requires classes with at least 5 students (count $\ge 5$), we evaluate our counts:

• Math ($6 \ge 5$) $\rightarrow$ Keep
• English ($1 \ge 5$) $\rightarrow$ Discard
• Biology ($1 \ge 5$) $\rightarrow$ Discard
• Computer ($1 \ge 5$) $\rightarrow$ Discard

Expected Output:

4. Naive Approach vs. Aggregate Grouping Approach

When beginners first approach grouping problems, they often attempt to solve them using row-level logic or incorrect syntax.

Why a Simple WHERE Clause Fails Here:

If we try to write:

-- INCORRECT APPROACH
SELECT class 
FROM courses 
WHERE COUNT(student) >= 5;

The database engine will throw an error. Why? Because WHERE operates on individual rows before any grouping happens, meaning aggregate functions like COUNT() have not been computed yet at the WHERE evaluation stage.

5. Crafting the Optimal SQL Solution

To solve this correctly, we must combine three core SQL clauses in the correct logical sequence:

1. FROM courses: Specifies the source table.
2. GROUP BY class: Collapses individual student rows into distinct class buckets.
3. HAVING COUNT(student) >= 5: Filters the aggregated groups based on our threshold condition.

Final Production Query

SELECT class
FROM courses
GROUP BY class
HAVING COUNT(student) >= 5;

6. Detailed Query Execution Flow

Let's trace how the database engine executes our query step-by-step:

7. Deep Dive: WHERE vs. HAVING in SQL

Understanding the difference between WHERE and HAVING is a classic SQL interview checkpoint.

8. Summary and Key Takeaways

• Composite Primary Keys: The primary key (student, class) ensures integrity by preventing duplicate student enrollments in the same class.
• Grouping Data: Use GROUP BY class when you need to condense multiple rows belonging to the same category into a single unified unit.
• Aggregation: Aggregate functions like COUNT(student) calculate metrics across the grouped buckets.
• Filtering Groups: Always use HAVING (not WHERE) when your filter condition relies on an aggregate calculation (e.g., group counts, sums, or averages).
• Execution Order: Remember the logical execution sequence of SQL clauses: FROM $\rightarrow$ WHERE $\rightarrow$ GROUP BY $\rightarrow$ HAVING $\rightarrow$ SELECT $\rightarrow$ ORDER BY.