What you will be able to do
In this project you build a small management report from the sample company database: headcount and payroll per department, hours worked on projects, rankings, and each department's share of the company. You will use joins, grouping, CTEs and window functions together, the way a real report is built: one small, checked step at a time.
The data is the fictional sample database from earlier lessons. Nothing here describes a real company.
The brief
A manager asks for one table with a row per department showing:
- how many people work there and what they cost in salary,
- how many project hours the department's people have logged,
- which department is the largest by payroll and what share of the payroll of all departments it is,
- who the highest-paid person is in each department.
Rather than write one giant query, we build it in steps and check each one.
Step 1: headcount and payroll
Start from employees and join their department. Employee 18 (Ravi) has no department, so use a LEFT JOIN and label the gap instead of losing the row:
SELECT COALESCE(d.name, '(no department)') AS department,
COUNT(*) AS headcount,
SUM(e.salary) AS payroll,
ROUND(AVG(e.salary), 0) AS avg_salary
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
GROUP BY COALESCE(d.name, '(no department)')
ORDER BY payroll DESC;+-----------------+-----------+---------+------------+
| department | headcount | payroll | avg_salary |
+-----------------+-----------+---------+------------+
| Engineering | 6 | 650000 | 108333.0 |
| Sales | 5 | 432000 | 86400.0 |
| Marketing | 3 | 242000 | 80667.0 |
| Support | 3 | 197000 | 65667.0 |
| Finance | 2 | 185000 | 92500.0 |
| (no department) | 1 | 95000 | 95000.0 |
+-----------------+-----------+---------+------------+Check: the headcounts add up to 20, every employee. The Research department has no employees, so it does not appear in a report that starts from employees. We will decide later how to handle that.
Step 2: hours per employee
Not everyone has worked on a project, so again we need a LEFT JOIN, and COALESCE turns the missing total into 0:
SELECT e.id, e.first_name,
COALESCE(SUM(a.hours), 0) AS total_hours,
COUNT(a.project_id) AS projects
FROM employees e
LEFT JOIN assignments a ON a.employee_id = e.id
GROUP BY e.id, e.first_name
ORDER BY total_hours DESC, e.id
LIMIT 8;+----+------------+-------------+----------+
| id | first_name | total_hours | projects |
+----+------------+-------------+----------+
| 3 | Kavya | 640 | 2 |
| 2 | Arjun | 520 | 2 |
| 4 | Rohan | 450 | 2 |
| 5 | Isha | 450 | 2 |
| 7 | Neha | 340 | 1 |
| 8 | Sameer | 310 | 1 |
| 14 | Omar | 260 | 1 |
| 19 | Diya | 240 | 1 |
+----+------------+-------------+----------+Note COUNT(a.project_id) rather than COUNT(*), so an employee with no assignments counts as 0 projects, not 1.
Step 3: staffing by project
The same joins can answer a project-level question: how many people, how many hours, and how much budget per hour for each project.
SELECT p.name AS project, p.budget,
COUNT(*) AS people,
SUM(a.hours) AS total_hours,
ROUND(1.0 * p.budget / SUM(a.hours), 0) AS budget_per_hour
FROM projects p
JOIN assignments a ON a.project_id = p.id
GROUP BY p.id, p.name, p.budget
ORDER BY budget_per_hour DESC;+-------------------+--------+--------+-------------+-----------------+
| project | budget | people | total_hours | budget_per_hour |
+-------------------+--------+--------+-------------+-----------------+
| Billing Migration | 300000 | 3 | 390 | 769.0 |
| Mobile App | 650000 | 4 | 1100 | 591.0 |
| Customer Portal | 400000 | 4 | 1160 | 345.0 |
| Spring Campaign | 120000 | 3 | 480 | 250.0 |
| Q3 Sales Push | 200000 | 5 | 1050 | 190.0 |
+-------------------+--------+--------+-------------+-----------------+1.0 * forces decimal division. Without it, integer division would drop the fraction in several databases.
Step 4: rank people inside their department
Now a window function. Rank employees by salary within each department and show how far each is from the department's average. A CTE keeps the window logic separate from the filter:
WITH ranked AS (
SELECT d.name AS department, e.first_name, e.salary,
RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS salary_rank,
ROUND(e.salary - AVG(e.salary) OVER (PARTITION BY e.department_id), 0) AS vs_dept_avg
FROM employees e
JOIN departments d ON d.id = e.department_id
)
SELECT department, first_name, salary, salary_rank, vs_dept_avg
FROM ranked
WHERE salary_rank <= 2
ORDER BY department, salary_rank, first_name;+-------------+------------+--------+-------------+-------------+
| department | first_name | salary | salary_rank | vs_dept_avg |
+-------------+------------+--------+-------------+-------------+
| Engineering | Meera | 180000 | 1 | 71667.0 |
| Engineering | Arjun | 125000 | 2 | 16667.0 |
| Engineering | Kavya | 125000 | 2 | 16667.0 |
| Finance | Lata | 115000 | 1 | 22500.0 |
| Finance | Omar | 70000 | 2 | -22500.0 |
| Marketing | Dev | 110000 | 1 | 29333.0 |
| Marketing | Anika | 68000 | 2 | -12667.0 |
| Sales | Vikram | 150000 | 1 | 63600.0 |
| Sales | Neha | 90000 | 2 | 3600.0 |
| Sales | Sameer | 90000 | 2 | 3600.0 |
| Support | Pooja | 85000 | 1 | 19333.0 |
| Support | Karan | 56000 | 2 | -9667.0 |
| Support | Sana | 56000 | 2 | -9667.0 |
+-------------+------------+--------+-------------+-------------+The rows are the top two (with ties) per department. Here the inner join is deliberate: a salary rank within a department makes no sense for someone who has no department.
Step 5: the department scorecard
Time to combine. Each block becomes a CTE:
dept_people: headcount and payroll per department (from Step 1).dept_hours: total project hours per department, found by joining assignments to the employee who did the work.top_earner: the highest-paid employee in each department, found withROW_NUMBER.
The final query joins them and uses window functions across the department rows for the share of company payroll and the payroll rank.
WITH dept_people AS (
SELECT department_id, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
),
dept_hours AS (
SELECT e.department_id, SUM(a.hours) AS hours
FROM assignments a
JOIN employees e ON e.id = a.employee_id
WHERE e.department_id IS NOT NULL
GROUP BY e.department_id
),
top_earner AS (
SELECT department_id, first_name || ' ' || last_name AS name, salary
FROM (
SELECT department_id, first_name, last_name, salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, id) AS rn
FROM employees
WHERE department_id IS NOT NULL
)
WHERE rn = 1
)
SELECT d.name AS department,
p.headcount,
p.payroll,
COALESCE(h.hours, 0) AS project_hours,
ROUND(100.0 * p.payroll / SUM(p.payroll) OVER (), 1) AS pct_of_payroll,
RANK() OVER (ORDER BY p.payroll DESC) AS payroll_rank,
t.name AS top_earner
FROM departments d
JOIN dept_people p ON p.department_id = d.id
LEFT JOIN dept_hours h ON h.department_id = d.id
JOIN top_earner t ON t.department_id = d.id
ORDER BY payroll_rank;+-------------+-----------+---------+---------------+----------------+--------------+--------------+
| department | headcount | payroll | project_hours | pct_of_payroll | payroll_rank | top_earner |
+-------------+-----------+---------+---------------+----------------+--------------+--------------+
| Engineering | 6 | 650000 | 2300 | 38.1 | 1 | Meera Iyer |
| Sales | 5 | 432000 | 1050 | 25.3 | 2 | Vikram Singh |
| Marketing | 3 | 242000 | 480 | 14.2 | 3 | Dev Patel |
| Support | 3 | 197000 | 0 | 11.5 | 4 | Pooja Reddy |
| Finance | 2 | 185000 | 350 | 10.8 | 5 | Lata Bose |
+-------------+-----------+---------+---------------+----------------+--------------+--------------+Walk through the choices. dept_hours is built from its own aggregation, then joined, which avoids the classic trap of joining people and assignments together and counting salaries once per assignment. LEFT JOIN dept_hours keeps a department even if nobody worked project hours (Support shows 0 hours). The || operator concatenates text in SQLite and PostgreSQL; see the database notes.
Step 6: check the report
A report that runs is not necessarily right. Compare it with direct totals:
SELECT COUNT(*) AS employees,
SUM(salary) AS total_payroll,
(SELECT SUM(hours) FROM assignments) AS total_hours
FROM employees;+-----------+---------------+-------------+
| employees | total_payroll | total_hours |
+-----------+---------------+-------------+
| 20 | 1801000 | 4180 |
+-----------+---------------+-------------+The scorecard's pct_of_payroll column is a share of the payroll of the five departments, which excludes Ravi (salary 95,000), because he has no department. The total payroll of all employees is therefore larger than the sum of the department payrolls, by exactly Ravi's salary. Verify the gap. The scorecard's project hours (2,300 + 1,050 + 480 + 0 + 350) do add up to the 4,180 total above, because Ravi has no assignments:
SELECT (SELECT SUM(salary) FROM employees) - (SELECT SUM(salary) FROM employees WHERE department_id IS NOT NULL) AS unassigned_payroll;+--------------------+
| unassigned_payroll |
+--------------------+
| 95000 |
+--------------------+If a check does not match, look for a join that dropped or duplicated rows. That is the most common source of wrong numbers in reports.
Differences between databases
- String concatenation.
||works in SQLite and PostgreSQL. MySQL usesCONCAT(a, ' ', b)by default, and SQL Server uses+orCONCAT. - Subquery alias. The inner subquery in
top_earnerhas no alias, which SQLite and PostgreSQL 16 and newer accept; MySQL, SQL Server and older PostgreSQL require one, for example) AS ranked. - Division.
1.0 *makes the division decimal everywhere; plain integer division differs between MySQL and the others. - Window functions and CTEs need MySQL 8.0 or newer. For more on those, see the MySQL window functions lesson and the MySQL CTE lesson.
Common mistakes
- Joining people to assignments, then summing salaries. Each salary is repeated once per assignment. Aggregate each side separately in a CTE first.
- Using an INNER JOIN where rows can be missing. Employees with no assignments vanish; use a
LEFT JOINandCOALESCE. - Using COUNT(*) after a LEFT JOIN. An unmatched row still counts as one. Count a column from the optional side.
- Not reconciling. Always compare a report total with a simple total from the source table.
- Writing the whole report in one go. Build and check each CTE on its own.
Exercise
Extend Step 5 so that Research, which has no employees, also appears, with a headcount of 0, a payroll of 0 and a NULL top earner. Hint: start from departments and use LEFT JOIN for every CTE, with COALESCE for the numbers. Show the department, headcount, payroll and top earner.
Show one possible answer
WITH dept_people AS (
SELECT department_id, COUNT(*) AS headcount, SUM(salary) AS payroll
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
),
top_earner AS (
SELECT department_id, first_name || ' ' || last_name AS name
FROM (
SELECT department_id, first_name, last_name,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC, id) AS rn
FROM employees
WHERE department_id IS NOT NULL
)
WHERE rn = 1
)
SELECT d.name AS department,
COALESCE(p.headcount, 0) AS headcount,
COALESCE(p.payroll, 0) AS payroll,
t.name AS top_earner
FROM departments d
LEFT JOIN dept_people p ON p.department_id = d.id
LEFT JOIN top_earner t ON t.department_id = d.id
ORDER BY payroll DESC;+-------------+-----------+---------+--------------+
| department | headcount | payroll | top_earner |
+-------------+-----------+---------+--------------+
| Engineering | 6 | 650000 | Meera Iyer |
| Sales | 5 | 432000 | Vikram Singh |
| Marketing | 3 | 242000 | Dev Patel |
| Support | 3 | 197000 | Pooja Reddy |
| Finance | 2 | 185000 | Lata Bose |
| Research | 0 | 0 | NULL |
+-------------+-----------+---------+--------------+Key points
- Build a report in layers: get each building block right on its own, then combine the blocks with CTEs.
- Joins connect the tables, GROUP BY summarises them, and window functions add ranks and shares without losing detail.
- Choose the join type on purpose: a LEFT JOIN keeps employees with no assignments, and COALESCE turns the resulting NULL totals into zero.
- Always reconcile the report against simple totals taken straight from the tables, such as the count of employees and the sum of salaries.
Check yourself
Final exam and certificate
Finish all 49 lessons to unlock a 15-question exam on SQL. Pass it and you get a certificate with a public link you can share.
Sign in to take the exam