Multiple CTEs

Finance wants every employee earning above their own department's average salary, a two-step calculation, first the department averages, then the comparison. One WITH clause can define both steps, named, in order.

Learning Objectives

• Define more than one CTE in a single WITH clause, comma-separated.
• Reference an earlier CTE from a later one in the same WITH clause.
• Know which direction referencing is actually allowed in.

Employees Earning Above Their Department's Average

dept_avg computes each department's average salary first. above_avg, defined right after it with a comma, not a second WITH, joins employees against dept_avg to keep only those earning more than their own department's average. The main query just reads the final, already-filtered result.

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
),
above_avg AS (
  SELECT e.first_name, e.last_name, e.salary, d.avg_salary
  FROM employees e
  JOIN dept_avg d ON e.department_id = d.department_id
  WHERE e.salary > d.avg_salary
)
SELECT * FROM above_avg;

Try It Yourself

Write a query, using two CTEs, that finds every department where the average salary exceeds 75000, then lists all employees in those departments, on MySQL.

WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
),
high_paying_depts AS (
  SELECT department_id FROM dept_avg WHERE avg_salary > 75000
)
SELECT e.* FROM employees e
JOIN high_paying_depts h ON e.department_id = h.department_id;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Name each CTE for what it represents (dept_avg, above_avg), not generically (cte1, cte2), the names are the whole readability benefit.
• Keep each CTE focused on one clear step, chaining several small, obvious steps reads far better than one giant CTE trying to do everything.
• Reach for a view instead once a chain of CTEs needs to be reused across more than one separate query.

Interview Questions

Summary

A single WITH clause can define multiple CTEs, comma-separated, with each one able to reference any CTE defined earlier in that same clause, never a later one. This turns a multi-step calculation into a readable, named pipeline, instead of one deeply nested query.

What's Next?

Recursive CTEs are next: a CTE that references itself, the only way to walk a hierarchy, like Beacon Robotics' manager chain, to an unknown depth.