SQL CTE (WITH Clause) Explained with Examples

Naming a step

Instead of repeating a subquery, define it once: WITH dept_avg AS (SELECT dept_id, AVG(salary) …), then join dept_avg in the main query. Each CTE exists only for the statement it belongs to.

A good habit for interview questions: write one CTE per idea ("monthly revenue", "ranked rows", "first order per user"), then select from the last one. You can test each step by selecting from it directly.

The example tables

Every example on this page uses these tables. They are small on purpose, so you can check each result by eye.

Examples

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, ROUND(a.avg_salary) AS dept_avg
FROM employees e
JOIN dept_avg a ON e.dept_id = a.dept_id
WHERE e.salary > a.avg_salary
ORDER BY e.name;

WITH monthly AS (
  SELECT EXTRACT(MONTH FROM order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY EXTRACT(MONTH FROM order_date)
),
ranked AS (
  SELECT month, revenue,
         revenue - LAG(revenue) OVER (ORDER BY month) AS change_from_prev
  FROM monthly
)
SELECT month, revenue, change_from_prev
FROM ranked
ORDER BY month;

Common mistakes

• Using a CTE in a later statement. A CTE only lives for the single statement it starts. Run it again (or create a view) if another query needs it.
• Filtering on a window function directly. WHERE ROW_NUMBER() OVER (…) = 1 is not allowed. Compute the row number in a CTE, then filter in the outer query.
• Forgetting the comma. Several CTEs are separated by commas and share one WITH: WITH a AS (…), b AS (…).

Practise CTEs (WITH clause)

Interview questions that use this concept, easiest first. Each one has the step-by-step visualizer and answer checking.

1. 1

   Monthly Listing Ratings

   Average star rating for each listing in each month.

   Easy✓ Solved
2. 2

   Largest Unique Number

   Find the largest number that appears exactly once.

   Easy✓ Solved
3. 3

   Most Watched Title per Genre

   Find the title with the highest total watch time in each genre, keeping ties.

   Medium✓ Solved
4. 4

   Org Chart Downstream Reports

   List everyone who reports, directly or indirectly, to a given manager along with their depth.

   Medium✓ Solved
5. 5

   Ordered Purchase Funnel

   Measure a three-step conversion funnel where the steps must happen in order.

   Medium✓ Solved
6. 6

   Each Rider's Third Completed Trip

   Return the third completed trip for every rider who has at least three.

   Medium✓ Solved

Want a full set? Work through the Subqueries & CTEs study plan.

Questions people ask

Do MySQL and PostgreSQL both support CTEs?

Yes: PostgreSQL for a long time, and MySQL since version 8.0, including WITH RECURSIVE.

Is a CTE faster than a subquery?

Usually they perform the same; modern optimisers inline simple CTEs. Use CTEs for readability.

Related concepts

• SQL Subqueries Explained: Scalar, Correlated and EXISTS
• SQL UNION vs UNION ALL: Combining Query Results
• SQL ROW_NUMBER vs RANK vs DENSE_RANK