SQL for Data Analysts (Part 5): CTEs — Write SQL Like a Human

Subqueries work. CTEs communicate. Here’s the difference, and why it matters.

You’ve written the JOIN. You’ve added the window function. The query does what you need it to do.
Then you look at it a week later and have absolutely no idea what it’s doing. That’s the subquery problem. They work. They nest. Past a certain depth, they stop being queries and start being archaeology.

CTEs — Common Table Expressions — are the fix. Not a new concept, not an advanced technique. Just a better way to structure logic that was already there.

So far in this series:

• Part 1 → Pulling data
• Part 2 → Summarizing data
• Part 3 → Combining data across tables
• Part 4 → Window functions
• Now: Part 5 → Writing SQL that other people (and future-you) can actually read.

What a CTE Actually Is

A CTE is a named, temporary result set. You define it at the top of your query, give it a name, and then reference it like a table.

The syntax looks like this:

WITH cte_name AS (
SELECT …
FROM …
WHERE …
)
SELECT *
FROM cte_name;

That’s it. The CTE exists only for the duration of the query. It doesn’t create a table, doesn’t persist anywhere. It’s scaffolding.

CTEs vs Subqueries — Same Result, Different Story

Both of these return the same data. Read them and notice which one you understand on the first pass.

The subquery version

SELECT
rep_name,
region,
total_sales
FROM (
SELECT
r.rep_name,
r.region,
SUM(o.amount) AS total_sales
FROM sales_reps r
LEFT JOIN sales_orders o ON r.rep_id = o.rep_id
WHERE o.status = 'paid'
GROUP BY r.rep_name, r.region
) rep_summary
WHERE total_sales > 10000
ORDER BY total_sales DESC;

The CTE version

Same logic, but structured using a CTE:

WITH rep_summary AS (
SELECT
r.rep_name,
r.region,
SUM(o.amount) AS total_sales
FROM sales_reps r
LEFT JOIN sales_orders o ON r.rep_id = o.rep_id
WHERE o.status = 'paid'
GROUP BY r.rep_name, r.region
)
SELECT
rep_name,
region,
total_sales
FROM rep_summary
WHERE total_sales > 10000
ORDER BY total_sales DESC;

The SQL is identical in what it does. The CTE version tells you what each piece is before it asks you to follow how the pieces connect.

CTEs don’t inherently make your SQL faster — and in some cases, they can even be slower depending on how your database engine executes them. They make your SQL readable — and readable SQL gets reviewed, trusted, and maintained. That’s the actual value.

1️⃣ Chained CTEs — Where the Real Power Is

You can chain multiple CTEs together in a single query. Each one builds on the last. This is where complex reporting logic stops being a wall of nested SQL and starts reading like a set of steps.

Business question: which reps are in the top 25% by paid sales this month, and what’s their average deal size?

WITH paid_orders AS (
  -- Step 1: isolate paid orders from this month
  SELECT
    rep_id,
    amount
  FROM sales_orders
  WHERE status = 'paid'
    AND DATE_TRUNC('month', order_date) = DATE_TRUNC('month', CURRENT_DATE)
),

rep_totals AS (
  -- Step 2: aggregate by rep
  SELECT
    r.rep_id,
    r.rep_name,
    r.region,
    COUNT(o.amount) AS deal_count,
    SUM(o.amount) AS total_sales,
    AVG(o.amount) AS avg_deal_size
  FROM sales_reps r
  LEFT JOIN paid_orders o 
    ON r.rep_id = o.rep_id
  GROUP BY r.rep_id, r.rep_name, r.region
),

percentile_cutoff AS (
  -- Step 3: calculate the 75th percentile threshold
  SELECT
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_sales) AS p75
  FROM rep_totals
)

-- Step 4: filter to top quartile reps
SELECT
  rt.rep_name,
  rt.region,
  rt.deal_count,
  rt.total_sales,
  ROUND(rt.avg_deal_size, 2) AS avg_deal_size
FROM rep_totals rt
CROSS JOIN percentile_cutoff pc
WHERE rt.total_sales >= pc.p75
ORDER BY rt.total_sales DESC;

Try writing that as nested subqueries. You can. It’ll work. It will also take twice as long to debug when a number looks off.

Chained CTEs let you test each step independently. Comment out everything after the second CTE, run just that, check the numbers. That’s the real workflow.

2️⃣ The LEFT JOIN + IS NULL Pattern — Now With a CTE

You’ve seen this pattern in Part 3. CTEs make it cleaner to reuse across a longer query.

Get Soumya Samikshya Tripathy’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Business question: which reps had zero orders last month?

WITH last_month_orders AS (
  SELECT DISTINCT rep_id
  FROM sales_orders
  WHERE DATE_TRUNC('month', order_date) =
       DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
)

SELECT
  r.rep_id,
  r.rep_name,
  r.region
FROM sales_reps r
LEFT JOIN last_month_orders o 
  ON r.rep_id = o.rep_id
WHERE o.rep_id IS NULL
ORDER BY r.region, r.rep_name;

The CTE makes the intent explicit: last_month_orders is doing one job. The outer query does another. Neither is buried inside the other.

3️⃣ Recursive CTEs — Org Charts and Hierarchies

Recursive CTEs are their own category. They’re how you walk hierarchical data — org charts, parent-child product categories, nested account structures.

Most analysts don’t use these every day. But the moment you have a manager_id column pointing back to the same table, this is the tool.

+--------+----------+------------+
| emp_id | emp_name | manager_id |
+--------+----------+------------+
| 1      | Priya    | NULL       |
| 2      | Arjun    | 1          |
| 3      | Meera    | 1          |
| 4      | Dev      | 2          |
| 5      | Sana     | 2          |
| 6      | Karan    | 3          |
+--------+----------+------------+

Business question: show me the full reporting chain under Priya, with each person’s level in the hierarchy.

A recursive CTE has two parts: a base case (where the query starts), and a recursive step (how it moves through the hierarchy).

Think of it as: start at the top, then repeatedly find “who reports to whom” until there’s nothing left to expand.

Priya
 ├── Arjun
 │    ├── Dev
 │    └── Sana
 └── Meera
      └── Karan

Now, let’s translate that into SQL:

WITH RECURSIVE org_chart AS (

    -- Base case: start at the top
    SELECT
        emp_id,
        emp_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: find each person's direct reports
    SELECT
        e.emp_id,
        e.emp_name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT
    REPEAT('    ', level - 1) || emp_name AS org_structure,
    level
FROM org_chart
ORDER BY level, emp_name;

The recursive CTE works in two parts: the base case (where to start) and the recursive step (keep going until there’s nothing left to join).

Recursive CTEs are supported in PostgreSQL, BigQuery, Snowflake, SQL Server, and most modern databases. MySQL added support in version 8.0. Check your platform before relying on it.

When to Use a CTE vs Other Options

Use a CTE when:

• Your logic has more than one meaningful step
• You’re reusing the same filtered dataset multiple times
• You want someone else (or yourself in two weeks) to understand what the query is doing
• You’re debugging and need to isolate a step

A subquery is fine when:

• The logic is short, one-shot, and self-explanatory
• It’s inside a WHERE clause doing a simple existence check (EXISTS, IN)

Consider a temp table or view when:

• Multiple separate queries need the same result set
• The intermediate dataset is large and you’re seeing performance issues

A note on performance: CTEs in most databases are not materialised by default — the database engine inlines them. If you’re using a CTE that gets referenced multiple times and it’s expensive, some databases let you use MATERIALIZED hint to compute it once. BigQuery and Snowflake handle this differently. Worth knowing when queries start running slow.

Why This Matters Beyond Syntax

The reports and dashboards that actually get used are the ones people trust. And people trust SQL they can read and verify.

When a senior analyst or an engineer reviews your query, a CTE tells them you thought about structure — not just the result. It’s a small signal, but it compounds.

More practically: in four years of analytics work, the queries that caused the most problems weren’t the ones with bugs. They were the ones that nobody wanted to touch because nobody could understand them quickly enough to feel safe making a change.

CTEs don’t prevent bugs. But they make bugs easier to find, and they make correct code easier to trust.

Practice

Try writing:

1. A single CTE that filters paid orders, then query it to get total sales per rep
2. Chain two CTEs: one for orders, one for rep totals, filter the final result to a specific region
3. Use a CTE to find reps with zero paid orders this month (LEFT JOIN + IS NULL pattern)
4. If your database supports it: write a recursive CTE on the employees table to return each person’s level and manager name

Dataset and solutions: github.com/TheAnalyticMind/sql-for-data-analysts/part-5-ctes

Where to practice: DB Fiddle (postgres mode) or LeetCode Database section.

Next in Part 6: The Real Interview Questions — 10 SQL patterns every analyst gets asked, what they’re actually testing, and a GitHub cheat sheet you’ll use more than you expect.