Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 49 of 49

Project: a company report

Build a multi-step company report in SQL that combines joins, grouping, CTEs and window functions, and check its totals against the raw data.

25 minPracticeUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonCombine joins, GROUP BY, CTEs and window functions to build a department scorecard step by step, and verify the report against the source tables.

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:

  1. how many people work there and what they cost in salary,
  2. how many project hours the department's people have logged,
  3. which department is the largest by payroll and what share of the payroll of all departments it is,
  4. 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:

sql
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;
result
+-----------------+-----------+---------+------------+
|   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:

sql
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;
result
+----+------------+-------------+----------+
| 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.

sql
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;
result
+-------------------+--------+--------+-------------+-----------------+
|      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:

sql
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;
result
+-------------+------------+--------+-------------+-------------+
| 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 with ROW_NUMBER.

The final query joins them and uses window functions across the department rows for the share of company payroll and the payroll rank.

sql
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;
result
+-------------+-----------+---------+---------------+----------------+--------------+--------------+
| 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:

sql
SELECT COUNT(*) AS employees,
       SUM(salary) AS total_payroll,
       (SELECT SUM(hours) FROM assignments) AS total_hours
FROM employees;
result
+-----------+---------------+-------------+
| 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:

sql
SELECT (SELECT SUM(salary) FROM employees) - (SELECT SUM(salary) FROM employees WHERE department_id IS NOT NULL) AS unassigned_payroll;
result
+--------------------+
| 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 uses CONCAT(a, ' ', b) by default, and SQL Server uses + or CONCAT.
  • Subquery alias. The inner subquery in top_earner has 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 JOIN and COALESCE.
  • 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
sql
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;
result
+-------------+-----------+---------+--------------+
| 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

1. Why are the building blocks written as separate CTEs first?
2. Employee 18 has no department. Which join keeps them in a report that starts from employees?
3. What does SUM(payroll) OVER () give in a query that has one row per department?
4. How can you check the report is right?

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