SQL CTEs: The WITH Clause, With Examples

Suppose you want the employees who earn more than their department's average salary. That takes two steps: work out each department's average, then compare every employee with it. A CTE writes the first step as a named result, dept_avg, that the main query can join to:

WITH dept_avg AS (
  SELECT department, ROUND(AVG(salary)) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT e.first_name, e.last_name, e.department, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON d.department = e.department
WHERE e.salary > d.avg_salary
ORDER BY e.department, e.salary DESC
LIMIT 8;

▶ Run this query in the SQL Playground

Read it top to bottom: first the departments and their averages, then the employees above them. The CTE exists only while this one statement runs. Nothing is stored in the database, and the name dept_avg cannot be used by the next query.

WITH cte_name AS (
  SELECT ...            -- any SELECT
)
SELECT ...
FROM cte_name;        -- use it like a table

CTEs are standard SQL. They work in PostgreSQL, SQL Server, Oracle, SQLite and MySQL from version 8.0 (MySQL 5.7 does not support them).

One WITH can define several CTEs, separated by commas, and a later CTE can use an earlier one. That turns a long query into a pipeline of small steps. Here the first CTE totals revenue per month, the second adds the previous month with LAG, and the main query calculates the change:

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month,
         ROUND(SUM(total), 2) AS revenue
  FROM orders
  WHERE status NOT IN ('cancelled', 'refunded')
    AND order_date >= '2024-01-01'
  GROUP BY month
),
with_previous AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS previous_revenue
  FROM monthly
)
SELECT month, revenue, previous_revenue,
       ROUND(revenue - previous_revenue, 2) AS change
FROM with_previous
ORDER BY month;

▶ Run this query in the SQL Playground

Each step can be tested on its own: replace the main query with SELECT * FROM monthly and you see just the first step. That is one of the most practical reasons to write CTEs.

Using one CTE twice

A CTE can be referenced more than once. This compares each product category's revenue with the overall average across categories, reading category_sales twice:

WITH category_sales AS (
  SELECT p.category, ROUND(SUM(oi.subtotal), 2) AS revenue
  FROM order_items oi
  JOIN products p ON p.id = oi.product_id
  GROUP BY p.category
)
SELECT category, revenue,
       CASE WHEN revenue > (SELECT AVG(revenue) FROM category_sales)
            THEN 'Above average' ELSE 'Below average' END AS compared
FROM category_sales
ORDER BY revenue DESC;

▶ Run this query in the SQL Playground

The same first example can be written with a subquery in FROM (a derived table). It gives the same rows; the CTE just puts the step first and names it:

SELECT e.first_name, e.salary, d.avg_salary
FROM employees e
JOIN (SELECT department, ROUND(AVG(salary)) AS avg_salary
      FROM employees
      GROUP BY department) d ON d.department = e.department
WHERE e.salary > d.avg_salary;

A CTE is mainly about readability. Whether it is faster than a subquery depends on the database: PostgreSQL 12+ and SQL Server usually inline a CTE into the main query, so the speed is the same. See subqueries and views.

Top N per group

Window functions cannot go in WHERE, so the usual way to keep the top rows per group is to number them in a CTE and filter in the main query. The two most expensive products in each category:

WITH ranked AS (
  SELECT category, name, price,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE rn <= 2
ORDER BY category, rn
LIMIT 10;

▶ Run this query in the SQL Playground

Second-highest salary

A classic interview question. DENSE_RANK gives equal salaries the same rank, so rank 2 is the second-highest distinct salary:

WITH ranked AS (
  SELECT first_name, last_name, salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT first_name, last_name, salary
FROM ranked
WHERE salary_rank = 2;

▶ Run this query in the SQL Playground

Finding duplicates

Several Playground products share a name. A CTE that counts each name makes the duplicates easy to list:

WITH name_counts AS (
  SELECT name, COUNT(*) AS copies, MIN(id) AS first_id
  FROM products
  GROUP BY name
)
SELECT name, copies, first_id
FROM name_counts
WHERE copies > 1
ORDER BY copies DESC, name
LIMIT 8;

▶ Run this query in the SQL Playground

To list every duplicate row except the first of each name, number the rows with ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) in the CTE and keep rn > 1. The same CTE in front of a DELETE removes them, in databases that allow a CTE before DELETE (SQL Server, PostgreSQL; SQLite and MySQL need a subquery instead).

Cohorts: when did customers first buy?

WITH first_orders AS (
  SELECT user_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY user_id
)
SELECT strftime('%Y', first_order_date) AS cohort_year,
       COUNT(*) AS new_customers
FROM first_orders
GROUP BY cohort_year
ORDER BY cohort_year;

▶ Run this query in the SQL Playground

Retention: who came back the next year?

WITH buyer_years AS (
  SELECT DISTINCT user_id, CAST(strftime('%Y', order_date) AS INTEGER) AS yr
  FROM orders
)
SELECT a.yr AS year,
       COUNT(*) AS buyers,
       COUNT(b.user_id) AS bought_again_next_year
FROM buyer_years a
LEFT JOIN buyer_years b ON b.user_id = a.user_id AND b.yr = a.yr + 1
WHERE a.yr < 2024
GROUP BY a.yr
ORDER BY a.yr;

▶ Run this query in the SQL Playground

15 of the 52 customers who bought in 2022 bought again in 2023. The CTE is joined to itself, which is something a subquery would have to repeat in full.

A recursive CTE refers to itself. It is how SQL walks a hierarchy of unknown depth, such as an org chart or a category tree, or generates a series of numbers or dates. It has two parts joined by UNION ALL:

WITH RECURSIVE org AS (
  -- anchor: the top of the org chart
  SELECT id, first_name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive part: people who report to someone already in org
  SELECT e.id, e.first_name, e.manager_id, o.level + 1
  FROM employees e
  JOIN org o ON e.manager_id = o.id
  WHERE o.level < 10            -- safety stop
)
SELECT level, COUNT(*) AS employees
FROM org
GROUP BY level
ORDER BY level;

WITH RECURSIVE numbers(n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT n FROM numbers;

Rules to remember: the anchor and recursive parts must return the same number of columns with compatible types; use UNION ALL (the standard requires it for the recursive part in most databases); and always make sure the recursion ends. SQL Server stops after 100 levels by default (OPTION (MAXRECURSION n) changes that); PostgreSQL has no default limit.

Six errors and how to avoid them.

WITH dept_avg AS (SELECT department, AVG(salary) AS a
                  FROM employees GROUP BY department)
SELECT * FROM dept_avg;

SELECT * FROM dept_avg;  -- error: no such table

WITH dept_avg AS (SELECT department, AVG(salary) AS a
                  FROM employees GROUP BY department)
SELECT * FROM dept_avg
WHERE a > 120000;

WITH a AS (SELECT ...)
WITH b AS (SELECT ...)
SELECT ...

WITH a AS (SELECT ...),
     b AS (SELECT ...)
SELECT ...

WITH a AS (SELECT ...),
     b AS (SELECT ...),
SELECT * FROM b;

WITH a AS (SELECT ...),
     b AS (SELECT ...)
SELECT * FROM b;

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n
)
SELECT * FROM n;

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 100
)
SELECT * FROM n;

SELECT id, first_name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id FROM employees e JOIN org o ON e.manager_id = o.id

SELECT id, first_name FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.first_name FROM employees e JOIN org o ON e.manager_id = o.id

-- "I rewrote it as a CTE, so it must be faster"

-- Compare EXPLAIN for both versions;
-- use a temp table with an index for a large
-- intermediate result that is reused often

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Using a CTE, total the order value per customer and list the five customers who spent the most. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH spend AS (
     SELECT user_id, ROUND(SUM(total), 2) AS total_spent
     FROM orders
     GROUP BY user_id
   )
   SELECT user_id, total_spent
   FROM spend
   ORDER BY total_spent DESC
   LIMIT 5;

   ▶ Run this query in the SQL Playground

   The CTE does the grouping; the main query only sorts and limits.

2. 2Easy Using a CTE, find the average price per category and show only the categories whose average is above 1,000. Table: products

   Show solution

   SQLite · runs in the Playground

   WITH category_avg AS (
     SELECT category, ROUND(AVG(price), 2) AS avg_price
     FROM products
     GROUP BY category
   )
   SELECT category, avg_price
   FROM category_avg
   WHERE avg_price > 1000
   ORDER BY avg_price DESC;

   ▶ Run this query in the SQL Playground

   Filtering the CTE in WHERE does the same job as HAVING in a single query.

3. 3Medium Show the highest-paid employee in each department. Table: employees

   Show solution

   SQLite · runs in the Playground

   WITH ranked AS (
     SELECT department, first_name, last_name, salary,
            ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
     FROM employees
   )
   SELECT department, first_name, last_name, salary
   FROM ranked
   WHERE rn = 1
   ORDER BY salary DESC;

   ▶ Run this query in the SQL Playground

   Number the employees within each department, then keep number 1.

4. 4Medium With two CTEs, find the average rating per product and then the number of products in each category whose average is 4 or more. Table: reviews, products

   Show solution

   SQLite · runs in the Playground

   WITH product_rating AS (
     SELECT product_id, AVG(rating) AS avg_rating
     FROM reviews
     GROUP BY product_id
   ),
   good_products AS (
     SELECT p.category
     FROM products p
     JOIN product_rating r ON r.product_id = p.id
     WHERE r.avg_rating >= 4
   )
   SELECT category, COUNT(*) AS well_rated_products
   FROM good_products
   GROUP BY category
   ORDER BY well_rated_products DESC, category;

   ▶ Run this query in the SQL Playground

   The second CTE uses the first; the main query counts what is left.

5. 5Medium Find the third-highest order total. Table: orders

   Show solution

   SQLite · runs in the Playground

   WITH ranked AS (
     SELECT total, DENSE_RANK() OVER (ORDER BY total DESC) AS rnk
     FROM orders
   )
   SELECT DISTINCT total
   FROM ranked
   WHERE rnk = 3;

   ▶ Run this query in the SQL Playground

   DENSE_RANK makes rank 3 the third distinct value even if totals repeat.

6. 6Hard List the customers whose first order was in 2024, with the date of that order. Show the first 8 by date. Table: orders, users

   Show solution

   SQLite · runs in the Playground

   WITH first_orders AS (
     SELECT user_id, MIN(order_date) AS first_order_date
     FROM orders
     GROUP BY user_id
   )
   SELECT u.first_name, u.last_name, f.first_order_date
   FROM first_orders f
   JOIN users u ON u.id = f.user_id
   WHERE f.first_order_date >= '2024-01-01'
   ORDER BY f.first_order_date, u.id
   LIMIT 8;

   ▶ Run this query in the SQL Playground

   The CTE finds each customer's first order across all years; filtering on 2024 afterwards keeps only the customers who were new that year.