How can we calculate department statistics in a CTE and rank departments by average salary?
Staging departmental aggregations in a CTE and applying window functions in the outer query produces clean multi-step analytical metrics.
Department league tables, performance benchmarking, executive analytics models.
WITH department_stats AS (
SELECT
department_id,
COUNT(*) AS employee_count,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
)
SELECT
department_id,
employee_count,
average_salary,
RANK() OVER (ORDER BY average_salary DESC) AS salary_rank
FROM department_stats;Practice typing production-grade SQL code for Multi-Step Analytical Query.