Count Student Number in Departments

Count Student Number in Departments

What is this problem about?

The "Count Student Number in Departments interview question" is a SQL challenge that involves relational data analysis. You are given two tables: Student (containing student details and their department ID) and Department (containing department names and IDs). Your task is to generate a report that lists every department name and the total number of students enrolled in each. Departments with zero students must also be included in the results.

Why is this asked in interviews?

Twitter and other companies ask the "Count Student Number in Departments coding problem" to evaluate a candidate's mastery of Outer Joins. It specifically tests if you understand the difference between INNER JOIN and LEFT JOIN. Using an inner join would exclude departments without students, which violates the problem's requirements. It evaluates basic reporting and data aggregation skills in a database context.

Algorithmic pattern used

This problem follows the LEFT JOIN and GROUP BY pattern.

1. Join: Use a LEFT JOIN starting from the Department table to the Student table. This ensures that all departments are kept in the intermediate result set, even if they have no matching records in the student table.
2. Aggregation: Group the results by the department name (or department ID).
3. Counting: Use COUNT(student_id) rather than COUNT(*) to ensure that departments with zero students return a count of 0 (since COUNT of a null value is 0) instead of 1.
4. Ordering: Sort the results as specified, usually by count in descending order and then alphabetically by department name.

Example explanation

Department Table: (1, Engineering), (2, Science), (3, Arts) Student Table: (Alice, 1), (Bob, 1), (Charlie, 2)

1. Left Join:
   • Engineering matches Alice, Bob.
   • Science matches Charlie.
   • Arts matches NULL.
2. Group and Count:
   • Engineering: 2
   • Science: 1
   • Arts: 0 Result: Engineering: 2, Science: 1, Arts: 0.

Common mistakes candidates make

• Using INNER JOIN: This is the most common error, which results in the "Arts" department being missing from the final list.
• COUNT(*) vs COUNT(column): Using COUNT(*) counts the row itself, so even if a department has no students, the joined row (Dept + NULL student) would count as 1.
• Ambiguous column names: Not specifying which table a column belongs to in the JOIN or GROUP BY clause.

Interview preparation tip

Always remember: if the requirement says "include items with zero counts," you almost certainly need a LEFT JOIN. This is a foundational "Database interview pattern" for reporting tasks.