CTEs & Window Functions SQL practice problemEasyVerified answer

Department Summary With a CTE

Use a CTE to return department name, employee count, and average salary.

  • CTEs
  • Joins
  • Subqueries
  • Aggregation
  • NULL handling

Interview brief

Understand the request

Finance analytics manager The department summary report is built using a CTE so the aggregation logic is readable and reusable.

Use a CTE to pre-compute department totals, then display each department with its headcount and total salary expense. Departments with no employees show 0. Return department_id, department_name, total_employees, total_salary_expense — ordered by total_salary_expense DESC, department_id.

Return

  • Return department_name, employee_count, and avg_salary.

Constraints

  • Define a WITH clause before the main SELECT.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

departments

  • department_idINTEGER
  • department_nameVARCHAR(50)

employees

  • employee_idINTEGER
  • salaryINTEGER
  • department_idINTEGER

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

A CTE is a named subquery declared with WITH before the main SELECT.

Hint 2

COUNT(e.employee_id) (not COUNT(*)) gives 0 for empty departments after a LEFT JOIN.

Hint 3

COALESCE(SUM(...), 0) turns the NULL sum of an empty department into 0.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH dept_summary AS (SELECT d.department_id, d.department_name, COUNT(e.employee_id) AS total_employees, COALESCE(SUM(e.salary), 0) AS total_salary_expense FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name) SELECT department_id, department_name, total_employees, total_salary_expense FROM dept_summary ORDER BY total_salary_expense DESC, department_id;

Why this works

CTEs exist for readability and reuse. They let you name a step, then build on it — turning a deeply-nested query into a top-to-bottom pipeline. This one pre-aggregates per department, then presents it.

Expected result

Use this output to verify values, aliases, ordering, and row count.

department_iddepartment_nametotal_employeestotal_salary_expense
2Engineering6695000
3Sales4432000
1Executive1250000
5Finance2175000
4Human Resources2160000
6Marketing00

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: