Watch 4 video solutions for Count Student Number in Departments, a medium level problem involving Database. This walkthrough by Everyday Data Science has 2,352 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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 | +-------------+----------------+
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.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery Count | O(D * S) | O(1) | Simple datasets or quick prototypes where readability matters more than performance |
| LEFT JOIN + GROUP BY | O(D + S) | O(D) | Best general solution; efficient aggregation and ensures departments with zero students appear |
