Department Headcount
HR wants a headcount for every department in departments, including departments that currently have nobody (headcount 0).
Return columns dept_name, headcount. Order by headcount descending, then dept_name ascending.
Schema
CREATE TABLE departments ( dept_id INTEGER PRIMARY KEY, dept_name TEXT NOT NULL ); CREATE TABLE employees ( emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER );
Sample data
departments
| dept_id | dept_name |
|---|---|
| 1 | Engineering |
| 2 | Finance |
| 3 | Marketing |
| 4 | Legal |
employees
| emp_id | name | dept_id |
|---|---|---|
| 1 | Asha | 1 |
| 2 | Bruno | 1 |
| 3 | Carla | 2 |
| 4 | Dmitri | 1 |
| 5 | Ebony | 3 |
| 6 | Femi | 3 |
| 7 | Gul | NULL |
Hint 1
Start FROM departments and LEFT JOIN employees so empty departments survive.
Hint 2
COUNT(e.emp_id) counts only matched employees; COUNT(*) would count the NULL row as 1.
Good practice for: Data Analyst, Business Analyst, Backend Developer
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.