SQL CTEs for Data Analysts

As reports grow, single queries turn into tangled nests of subqueries that nobody — including their author a week later — can read. Common table expressions, written with the WITH keyword, fix this. A CTE is a named intermediate result you define at the top of the query and then use like a table below. The logic reads top to bottom, each step has a name, and you can reference a step more than once. For an analyst who writes long reporting queries daily, CTEs are the difference between maintainable and unmaintainable SQL.

This tutorial builds on subqueries — a CTE is essentially a named subquery. It sits in the intermediate stretch of the SQL for analytics path.

The basic WITH syntax

Define a CTE with WITH name AS (query), then select from it:

-- orders
order_id | customer | city      | amount
---------+----------+-----------+-------
1001     | Aarti    | Hyderabad | 1200
1002     | Bhaskar  | Hyderabad | 8500
1003     | Chitra   | Pune      | 1200
1004     | Devan    | Chennai   | 600
1005     | Esha     | Pune      | 4000

"Total revenue per city, then only cities above 5000" reads cleanly as a CTE:

WITH city_revenue AS (
    SELECT city, SUM(amount) AS revenue
    FROM orders
    GROUP BY city
)
SELECT city, revenue
FROM city_revenue
WHERE revenue > 5000
ORDER BY revenue DESC;

city      | revenue
----------+--------
Hyderabad | 9700

The city_revenue CTE computes the per-city totals; the main query filters them. The exact same result is possible with a subquery in FROM, but the CTE names the intermediate step, so the intent is obvious at a glance.

Chaining CTEs into a pipeline

The real power appears when you chain CTEs, each building on the last. Separate them with commas under one WITH. Here is a two-step pipeline: aggregate per city, then rank and label them.

WITH city_revenue AS (
    SELECT city, SUM(amount) AS revenue
    FROM orders
    GROUP BY city
),
ranked AS (
    SELECT city, revenue,
           RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
    FROM city_revenue
)
SELECT city, revenue, revenue_rank
FROM ranked
WHERE revenue_rank <= 2;

city      | revenue | revenue_rank
----------+---------+-------------
Hyderabad | 9700    | 1
Pune      | 5200    | 2

Each CTE reads like a paragraph: "first compute city revenue, then rank it, then keep the top two." Compare that to the same logic crammed into nested subqueries — the CTE version is far easier to write correctly and to hand to a colleague. The RANK() OVER (...) here is a window function, which pairs with CTEs constantly.

Referencing a CTE more than once

An inline subquery must be repeated everywhere it is used; a CTE can be named once and referenced multiple times. To compare each city's revenue to the overall average:

WITH city_revenue AS (
    SELECT city, SUM(amount) AS revenue
    FROM orders
    GROUP BY city
)
SELECT city, revenue,
       (SELECT AVG(revenue) FROM city_revenue) AS avg_revenue
FROM city_revenue
ORDER BY revenue DESC;

city_revenue is used both in the main FROM and inside the scalar subquery, without duplicating its definition. This reuse is a genuine advantage over inline subqueries when the same intermediate result feeds several parts of a query.

Recursive CTEs for hierarchies

A recursive CTE references itself to walk hierarchical data of unknown depth — an org chart, a category tree — which a single self join cannot handle. It has a base case and a recursive step unioned together:

WITH RECURSIVE chain AS (
    SELECT emp_id, emp_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL          -- base case: the top
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.manager_id, c.level + 1
    FROM employees e
    JOIN chain c ON e.manager_id = c.emp_id   -- recursive step
)
SELECT emp_name, level FROM chain ORDER BY level;

The base case selects the top-level employee; the recursive step repeatedly attaches each person's reports, incrementing a level counter until no new rows are found. Recursive CTEs solve multi-level problems cleanly, though most day-to-day analyst reporting uses ordinary, non-recursive CTEs.

Common mistakes

• Expecting a CTE to persist. A CTE exists only for the single statement that defines it. It is not a temporary table you can reuse in the next query.
• Assuming CTEs are always faster. They usually match subquery performance, but some databases materialize them, which can hurt. Do not adopt CTEs for speed; adopt them for clarity.
• Recursive CTE with no termination. If the recursive step never stops adding rows — for instance, a cycle in the hierarchy — the query runs away. Ensure the data is a true tree or add a depth guard.
• Over-splitting trivial queries. Wrapping a one-line query in three CTEs adds noise. Use CTEs when they genuinely clarify multi-step logic.

In interviews

CTE questions test structured thinking: "using CTEs, find the top two cities by revenue and their rank", or "walk the management chain from a given employee". Interviewers like CTEs because a candidate's version reveals how they decompose a problem into named steps. A strong answer names each CTE meaningfully and explains why the pipeline reads more clearly than nested subqueries. Recursive CTEs come up specifically for hierarchy questions.

Where this fits in your learning path

CTEs are the readable form of the subqueries you learned earlier, and they pair naturally with window functions for ranked, multi-step reports. For single-level hierarchies a self join still suffices; recursive CTEs extend that to any depth. Structuring queries well with CTEs is an expected skill on the data analyst roadmap.