Twitter SQL interview Questions – Leetcode 580 – Count Student Number in Departments

Problem Description –

A university uses 2 data tables, student and department, to store data about its students and the departments associated with each major.

Write a query to print 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).

Sort your results by descending number of students; if two or more departments have the same number of students, then sort those departments alphabetically by department name.

Table – Student

Table – Department

Output –

Difficulty Level – Medium

Solution –

SELECT
    d.dept_name,
    COUNT(s.student_id) AS student_number
FROM department AS d 
LEFT JOIN student AS s
ON d.dept_id = s.dept_id
GROUP BY 1
ORDER BY 2 DESC, 1 ASC

For the solution, we are doing a left join because we do not want to lose any data from the department table because some department doesn’t have a student as specified in the question. Then we are just grouping by the department and Ordering the result as specified in the question. One thing to note is that in the COUNT() function, we used s.tudent_id instead of * because if you use COUNT(*) it will also count the null rows for the department which doesn’t have any students and your answer will be incorrect. Using a column name in the COUNT() function makes sure that null values are not getting counted.