The Roster

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain: SQL
Difficulty: easy
Seniority: L5

Problem

Each row of employees is one employee, and user_id links that employee to a platform account when one exists. Count each department's employees that have a linked account, listing the departments with the most linked employees first.

Every department, counted head by head.

Tables

employees

Columns: employee_id INTEGER, emp_name TEXT, department TEXT, salary REAL, manager_id INTEGER, hire_date TEXT, user_id INTEGER

employees: 10 of 200 rows
employee_id	emp_name	department	salary	manager_id	hire_date	user_id
1	Alice Chen	Engineering	55731	NULL	2021-02-02	100
2	Bob Martinez	Product	61462	NULL	2022-03-03	197
3	Carol Wu	Design	67193	NULL	2023-04-04	294
4	David Kim	Marketing	72924	NULL	2024-05-05	391
5	Eve Johnson	Sales	78655	NULL	2025-06-06	488
6	Frank Patel	HR	84386	NULL	2026-07-07	585
7	Grace Lee	Finance	55117	NULL	2020-08-08	682
8	Hank Brown	Operations	60848	NULL	2021-09-09	779
9	Iris Davis	Data	66579	NULL	2022-10-10	876
10	Jake Wilson	Legal	72310	NULL	2023-11-11	973

Expected output

First 5 of 10 rows
department	linked_employees
Legal	20
Sales	18
Product	18
Operations	18
Marketing	18

Worked solution and explanation

What this really tests

This reads like a plain headcount, and that is the disguise. It is really a question about what a count counts. On this table every department has exactly 20 rows, so COUNT(*) hands back ten identical numbers and the 'most first' ordering has nothing to order. The account link lives in user_id, which is empty for a few employees, and only a count over that column sees the gap: 'Legal' keeps all 20 and the other nine departments drop to 18. Count rows instead and you ship ten 20s, nine of them wrong, with 'Legal' sitting wherever the engine happened to leave it.

Building the answer

Step 1: Collapse to one row per department

GROUP BY department gives one output row per department. Apart from the grouping key, the only column in employees that matters is the one recording the account link.

Step 2: Count the column, not the row

COUNT(user_id) skips every row where user_id is NULL, which is exactly the set of employees without a linked account. COUNT(*) would count every employee, linked or not.

Step 3: Sort on the count, largest first

ORDER BY linked_employees DESC puts 'Legal' on top. The other nine tie at 18, and tied rows are accepted in any order, so no second key is owed.

Counting rows: COUNT(*) returns 20 for all ten departments. The ordering decides nothing, and the result claims every employee is linked, which is false for 18 of them.
Counting linked accounts: COUNT(user_id) returns 20 for 'Legal' and 18 for the rest, so 'Legal' leads and the unlinked employees drop out of the count.

The solution

Linked employees per department

SELECT department, COUNT(user_id) AS linked_employees
FROM employees
GROUP BY department
ORDER BY linked_employees DESC

Common follow-up questions

What share of each department's employees is linked? (Divides `COUNT(user_id)` by `COUNT(*)` and watches for integer division.)
How would you list departments from a reference table that currently have no employees at all? (Needs a join that keeps the unmatched departments, with their count shown as 0.)
If only the top three departments were wanted and two tied at the cut, what would you do? (A `LIMIT` that cuts through a tie needs a stated tie rule.)