18

CHAPTER 18

CTEs - The WITH Clause

Named subqueries that make complex logic readable.

6

Learning Blocks

SQL

Interactive Queries

In This Chapter

Concepts you'll master

Defining a CTE with WITH ... AS (...)

Using multiple CTEs in sequence

Recursive CTEs for hierarchical data

CTE vs subquery — when to use each

16.1 - Basic CTEs

SQL QUERY

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_sal,
        e.salary - d.avg_sal AS diff
FROM employees e
JOIN dept_avg d ON e.department = d.department
ORDER BY e.department, diff DESC;

16.2 - Multiple CTEs

SQL QUERY

WITH
order_totals AS (
    SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent
    FROM orders GROUP BY customer_id
),
customer_tier AS (
    SELECT customer_id, spent,
        CASE WHEN spent > 100000 THEN 'VIP'
            WHEN spent > 10000 THEN 'Regular'
            ELSE 'New' END AS tier
    FROM order_totals
)
SELECT c.name, t.spent, t.tier
FROM customers c
JOIN customer_tier t ON c.customer_id = t.customer_id
ORDER BY t.spent DESC;

✅ Pro Tips: Rule of thumb: if you need to reference the same subquery twice, use a CTE. If a subquery is used once and is simple, inline it. If it is used 3+ times, consider a view or temp table.

💡 Engineering Insight: CTEs do not always improve performance — in some databases they are materialised (computed once and cached), in others they are inlined as subqueries. PostgreSQL inlines by default; you can force materialisation with the MATERIALIZED keyword. Always check your EXPLAIN plan.

Exercise

Practice your SQL skills

SQL EDITOR

Interactive Query Environment

Press semicolon (;) to auto-run query

Tasks

Complete all SQL challenges

Create a CTE to calculate total spending per customer.

Current Task

Create a CTE for orders where total is greater than 50000 and display them.

Create a CTE to count total orders per customer and show customer names with their order count.

Create a CTE to calculate average product price and find products priced above average.

Use multiple CTEs to find customers who have spent more than 50000 in total.

Need help solving this task?