Count Employees Per Department

SQL coding challenge · Difficulty: easy · +50 XP

Problem

The company wants an org chart overview. Show every department and how many employees it currently has. Departments with no staff must still appear.

Tables

Table: Employee

| employee_id | name | department_id |
| --- | --- | --- |
| 1 | Krishna | 1 |
| 2 | Parvati | 2 |
| 3 | Ashna | 3 |
| 4 | Pinki | 1 |
| 5 | Shruti | 4 |

Table: Department

| department_id | department_name |
| --- | --- |
| 1 | HR |
| 2 | Finance |
| 3 | IT |
| 4 | Sales |

Expected Output

| department_name | employee_count |
| --- | --- |
| Finance | 1 |
| HR | 2 |
| IT | 1 |
| Sales | 1 |

• Return: department_name, employee_count
• Departments with zero employees must appear with count = 0
• Sort by employee_count descending