CTEs & Window Functions SQL practice problemMediumVerified answer

Multiple CTEs — Department Productivity

Return a multi-CTE department summary combining headcount, average salary, and top salary.

  • CTEs
  • Joins
  • Subqueries
  • Aggregation
  • CASE expressions

Interview brief

Understand the request

HR analytics director The comprehensive workforce report chains multiple CTEs — headcount, salary stats, and top earner — into one output.

Use two CTEs — one for salary stats, one for project counts — then join them. Show department_id, department_name, avg_salary, total_projects, and productivity_ratio (total_projects / employee_count, 0 when no employees). Return rows ordered by department_name. Round avg_salary and productivity_ratio to 2 decimals.

Return

  • Return department_name, employee_count, avg_salary, and max_salary.

Constraints

  • Use multiple WITH clauses chained together before the final 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

projects

  • project_idINTEGER
  • department_idINTEGER

Hints, when you need them

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

Hint 1

Declare multiple CTEs by separating them with commas: WITH a AS (...), b AS (...).

Hint 2

Each CTE is independent here; the final SELECT joins them on department_id.

Hint 3

Guard the ratio against divide-by-zero with a CASE on employee_count.

Verified SQL answer

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

Reveal solution and explanation
WITH dept_salary AS (SELECT d.department_id, d.department_name, ROUND(AVG(e.salary), 2) AS avg_salary, COUNT(e.employee_id) AS employee_count FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id GROUP BY d.department_id, d.department_name), dept_projects AS (SELECT d.department_id, COUNT(p.project_id) AS total_projects FROM departments d LEFT JOIN projects p ON d.department_id = p.department_id GROUP BY d.department_id) SELECT s.department_id, s.department_name, s.avg_salary, p.total_projects, CASE WHEN s.employee_count = 0 THEN 0 ELSE ROUND(p.total_projects * 1.0 / s.employee_count, 2) END AS productivity_ratio FROM dept_salary s INNER JOIN dept_projects p ON s.department_id = p.department_id ORDER BY s.department_name;

Why this works

Multiple CTEs let you compute several independent aggregates at their own grain, then stitch them together. This is the readable alternative to several correlated subqueries or a tangle of nested joins.

Expected result

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

department_iddepartment_nameavg_salarytotal_projectsproductivity_ratio
2Engineering115833.3330.5
1Executive25000000
5Finance8750010.5
4Human Resources8000010.5
6MarketingNULL10
3Sales10800010.25

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics: