List Departments with Student Counts Including Zero
Company: Point72
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
Students
+----+--------+---------+
| id | name | dept_id |
+----+--------+---------+
| 1 | Alice | 1 |
| 2 | Bob | 2 |
| 3 | Carol | 1 |
+----+--------+---------+
Departments
+----+------------+
| id | dept_name |
+----+------------+
| 1 | CS |
| 2 | Math |
| 3 | Physics |
+----+------------+
##### Scenario
University dashboard must display every department and how many students it currently has, including departments with zero students.
##### Question
Write a SQL query that lists each department name and the corresponding student count. Departments with no students should appear with count = 0.
##### Hints
LEFT JOIN Departments to Students, then GROUP BY department ID or name.
Overview: This question evaluates proficiency with relational aggregation and join operations for counting related records across tables. It is commonly asked to assess understanding of SQL joins and grouping within the Data Manipulation (SQL/Python) domain and tests practical application of handling absent relationships rather than purely conceptual theory.
Given the Students and Departments tables, write a SQL query that lists each department name and the corresponding number of students in that department. Departments with no students must still appear in the result with a student_count of 0.
Tables
Students(id INTEGER, name VARCHAR, dept_id INTEGER)
Departments(id INTEGER, dept_name VARCHAR)
Hints
- Start from the Departments table and LEFT JOIN to Students on dept_id.
- Use COUNT(s.id) so that departments with no matching students return 0.