Cheatsheet Pages / SQL Cheatsheet
How to name an intermediate result in SQL — WITH (CTE)
A query needs an intermediate result — each department's average salary — before the real question can be asked.
Every example runs against the cheatsheet's sample tables — departments(id, name), employees(id, name, dept_id, salary, manager_id, hired_on) and orders(id, emp_id, amount, ordered_on) — and was executed and verified against SQLite, so you can paste it in and get the same result.
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary FROM employees e
JOIN dept_avg a ON e.dept_id = a.dept_id
WHERE e.salary > a.avg_salary;
-- Alice (above dept avg 101000) / David (above 70000): paid more than their own department's average
-- CTEs make complex queries read top-to-bottom instead of inside-out; MySQL 8.0+ / PostgreSQL / SQLite 3.8.8+ support them