Skip to main content
Talentd

Count Student Number in Departments - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Twitter
Practice this problem

Problem Statement

Table: Student

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| student_id   | int     |
| student_name | varchar |
| gender       | varchar |
| dept_id      | int     |
+--------------+---------+
student_id is the primary key (column with unique values) for this table.
dept_id is a foreign key (reference column) to dept_id in the Department tables.
Each row of this table indicates the name of a student, their gender, and the id of their department.

 

Table: Department

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| dept_id     | int     |
| dept_name   | varchar |
+-------------+---------+
dept_id is the primary key (column with unique values) for this table.
Each row of this table contains the id and the name of a department.

 

Write a solution to report the respective department name and number of students majoring in each department for all departments in the Department table (even ones with no current students).

Return the result table ordered by student_number in descending order. In case of a tie, order them by dept_name alphabetically.

The result format is in the following example.

 

Example 1:

Input: 
Student table:
+------------+--------------+--------+---------+
| student_id | student_name | gender | dept_id |
+------------+--------------+--------+---------+
| 1          | Jack         | M      | 1       |
| 2          | Jane         | F      | 1       |
| 3          | Mark         | M      | 2       |
+------------+--------------+--------+---------+
Department table:
+---------+-------------+
| dept_id | dept_name   |
+---------+-------------+
| 1       | Engineering |
| 2       | Science     |
| 3       | Law         |
+---------+-------------+
Output: 
+-------------+----------------+
| dept_name   | student_number |
+-------------+----------------+
| Engineering | 2              |
| Science     | 1              |
| Law         | 0              |
+-------------+----------------+

Approach Overview

Problem Overview: You have two tables: Student and Department. Each student belongs to a department through dept_id. The task is to count how many students belong to each department, including departments with zero students, and return the result sorted by student count (descending) and department name (ascending).

Approach 1: Correlated Subquery Count (O(D * S) time, O(1) space)

This approach iterates through each department and calculates the number of students using a correlated subquery. For every row in Department, a COUNT() query scans the Student table where dept_id matches. The logic is straightforward and keeps the query readable, but the database may execute the subquery repeatedly for each department. When the number of departments or students grows, the repeated scans make this less efficient.

Approach 2: LEFT JOIN + GROUP BY (O(D + S) time, O(D) space)

The optimal solution joins the Department table with Student using a LEFT JOIN. This ensures every department appears in the result, even when no matching students exist. After joining, use GROUP BY department.dept_name and COUNT(student.student_id) to compute the number of students in each department. Because COUNT() ignores NULL values, departments without students naturally produce a count of zero. Finally, apply ORDER BY student_number DESC, dept_name ASC to satisfy the sorting requirement.

This pattern—LEFT JOIN followed by GROUP BY—is a common aggregation technique in SQL and database interview questions. It ensures completeness of results while efficiently aggregating rows across tables. Understanding how joins interact with aggregate functions is key to solving many relational data problems.

Recommended for interviews: The LEFT JOIN + GROUP BY approach is what interviewers expect. It demonstrates that you understand join semantics, aggregation with COUNT(), and how to include rows with no matches. Mentioning the subquery approach first shows baseline understanding, but using the join-based aggregation shows stronger SQL skills.

Solution

We can use a left join to join the Department table and the Student table on dept_id, and then group by dept_id to count the number of students in each department. Finally, we can sort the result by student_number in descending order and dept_name in ascending order.

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Correlated Subquery CountO(D * S)O(1)Simple datasets or quick prototypes where readability matters more than performance
LEFT JOIN + GROUP BYO(D + S)O(D)Best general solution; efficient aggregation and ensures departments with zero students appear

Video Solution

LeetCode Medium 580 "Student Number in Departments" Twitter Interview SQL Question with Explanation • Everyday Data Science • 2,446 views views

Watch 2 more video solutions →

Frequently Asked Questions

Is Count Student Number in Departments easy or hard?
The problem is considered Medium because it tests multiple SQL concepts together: join semantics, aggregation, NULL handling, and sorting. Developers comfortable with LEFT JOIN and GROUP BY patterns usually solve it quickly.
Count Student Number in Departments Python/Java solution
This problem is typically solved using SQL rather than Python or Java because it operates directly on database tables. The correct query uses LEFT JOIN and GROUP BY to compute counts. In application code, the SQL query would be executed through a database driver or ORM.
How to solve Count Student Number in Departments in O(n)?
Use a LEFT JOIN between Department and Student on dept_id, then group the result using GROUP BY department name. COUNT(student_id) calculates how many students belong to each department. Since the join and aggregation scan the rows once, the overall complexity is roughly linear relative to the number of records.
What is the best approach for Count Student Number in Departments?
The most efficient solution uses a LEFT JOIN between the Department and Student tables followed by GROUP BY. This ensures every department appears in the result, even if no students belong to it. COUNT(student_id) aggregates the number of students per department, and the final result is sorted by student count and department name.
Is Count Student Number in Departments asked at Google/Amazon/Meta?
SQL aggregation and join problems similar to this frequently appear in interviews at companies like Amazon, Google, and Meta. Candidates are expected to understand join types, grouping, and how aggregate functions behave with NULL values.
What data structure is used in Count Student Number in Departments?
This problem relies on relational database operations rather than traditional data structures. The key concepts are SQL joins, grouping, and aggregation functions such as COUNT(). Internally, databases often use hash aggregation or sorting mechanisms to compute grouped counts efficiently.
What is the time complexity of Count Student Number in Departments?
The optimal LEFT JOIN + GROUP BY solution runs in approximately O(D + S) time where D is the number of departments and S is the number of students. The database performs a join and aggregation pass across the rows. Space complexity is O(D) for storing grouped results.

Ready to solve this problem?

Practice Count Student Number in Departments with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary