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.
Quick Answer: 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.