EasyCTEs & Recursive

Basic CTE: average salary with department names

The query

SQL

WITH dept_avg AS (
  SELECT
    dept_id,
    ROUND(AVG(salary), 2) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT
  d.dept_name,
  da.avg_salary
FROM dept_avg da
JOIN departments d ON da.dept_id = d.dept_id
ORDER BY da.avg_salary DESC;

Tested against PostgreSQL 16

Note

“CTEs (WITH clause) make complex queries readable. Same as subquery but defined upfront.

Tables referenced

employees10 cols
departments5 cols