Pathubs SQL Curriculum • Module 12

SQL Common Table Expressions (CTEs)

Master modular and readable SQL problem solving: understand WITH syntax, eliminate messy inside-out subqueries, chain multiple sequential CTEs, perform multi-step data transformations, and understand statement-level scope.

⏱️ Estimated Time:35 Minutes

🎯 Level:Intermediate Analytical SQL

📊 Track:Data Analytics & Backend Engineering

✨ Mode:Interactive CTE Lab

Introduction: The Problem With Deeply Nested Subqueries

As analytical requirements grow, writing subqueries inside subqueries creates queries that are written and read inside-out:

Messy Nested Subqueries

Hard to read, difficult to debug, and requires reading from the deepest inner query outward.

Clean Modular CTEs

Breaks complex logic into sequential, readable named steps: Step 1 ➔ Step 2 ➔ Final Result.

What Is a CTE? (The Core Mental Model)

Core Mental Model

CTE (Common Table Expression): Give a query result a temporary name, then use that named result in your main query.

WITH high_salary AS (
    SELECT *
    FROM employees
    WHERE salary > 50000
)
SELECT *
FROM high_salary;

Diagram 1: The Step-by-Step CTE Flow

Source Table
employees

── WITH high_salary AS (...) ──▶

Named Intermediate Step
high_salary

── SELECT * FROM high_salary ──▶

Final Clean Output
Qualified Records

Basic CTE Syntax & Structure

Every basic CTE consists of five fundamental syntax components:

WITH: The keyword that begins the CTE definition block.
cte_name: The custom descriptive name assigned to the temporary result.
AS: Introduces the query definition.
(...): Parentheses enclosing the inner SELECT query.
Main Query: The final statement that queries from cte_name.

CTE Step-by-Step Walkthrough

Consider three employee records:

id	name	salary	CTE Filter (salary > 50000)
1	Rahul	$60,000	Included in high_salary
2	Amit	$40,000	Excluded
3	Priya	$80,000	Included in high_salary

The CTE first filters rows into a virtual named dataset (high_salary). The outer query then reads from high_salary as if it were a clean, pre-filtered table.

CTE vs. Subquery: Major Conceptual Comparison

Comparing identical analytical tasks written with a Subquery vs a CTE:

Subquery Approach (Inside-Out):
SELECT *
FROM (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
) AS dept_stats
WHERE avg_sal > 70000;

CTE Approach (Top-to-Bottom):
WITH dept_stats AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
)
SELECT *
FROM dept_stats
WHERE avg_sal > 70000;

Diagram 2: Top-to-Bottom Flow (CTE) vs Inside-Out Nesting (Subquery)

SUBQUERY

Main SELECT
  └── (Subquery inside FROM)
      └── WHERE filter

CTE

Step 1: WITH named_step AS (...)
  ↓
Step 2: SELECT * FROM named_step

CTE With Aggregation: Computing Totals Before Filtering

In data analytics, you often need to calculate metrics in Step 1 and apply secondary filters in Step 2:

WITH department_sales AS (
    SELECT
        department,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY department
)
SELECT *
FROM department_sales
WHERE total_sales > 100000;

Multiple Chained CTEs (Comma Separation)

You can define multiple sequential CTEs within a single WITH statement by separating them with commas:

WITH
monthly_totals AS (
    SELECT
        DATE_TRUNC('month', order_date) AS order_month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
top_performing_months AS (
    -- CTE 2 queries CTE 1!
    SELECT *
    FROM monthly_totals
    WHERE revenue > 5000
)
SELECT *
FROM top_performing_months
ORDER BY revenue DESC;

Diagram 3: Chained Multi-Step CTE Pipeline

Step 1: CTE 1
monthly_totals

── Feeds Into ──▶

Step 2: CTE 2
top_performing_months

── Final SELECT ──▶

Result
Executive Report

CTE With JOIN Operations

A CTE can encapsulate complex joins, simplifying subsequent downstream queries:

WITH customer_orders AS (
    SELECT
        c.id AS customer_id,
        c.name AS customer_name,
        o.amount
    FROM customers AS c
    INNER JOIN orders AS o ON c.id = o.customer_id
)
SELECT customer_name, SUM(amount) AS total_spent
FROM customer_orders
GROUP BY customer_name;

CTE With CASE Categorization

Computing categorical labels in a CTE allows you to filter on that newly created label in the main query:

WITH employee_tiers AS (
    SELECT
        name,
        salary,
        CASE
            WHEN salary >= 90000 THEN 'High'
            WHEN salary >= 65000 THEN 'Medium'
            ELSE 'Low'
        END AS salary_tier
    FROM employees
)
SELECT *
FROM employee_tiers
WHERE salary_tier = 'High';

CTE With Date & Time Analysis

Aggregating time buckets with DATE_TRUNC inside a CTE:

WITH quarterly_revenue AS (
    SELECT
        DATE_TRUNC('quarter', order_date) AS quarter_start,
        SUM(amount) AS total_revenue
    FROM orders
    GROUP BY DATE_TRUNC('quarter', order_date)
)
SELECT *
FROM quarterly_revenue
ORDER BY quarter_start ASC;

Multi-Step Analytical Pipelines

A complete analytics pipeline converting raw order events into actionable management insights:

Raw Transactions: Granular timestamped orders.
Step 1 (Monthly Aggregation): Total monthly sales volume.
Step 2 (Growth Benchmark): Identifying high-performing months.
Final Result: Clean executive dashboard view.

CTE Statement Scope & Lifecycle

Crucial Concept: Single-Statement Lifecycle

A CTE exists only for the single SQL statement in which it is defined. You cannot execute a CTE in Query 1 and attempt to query it in Query 2!

Diagram 4: Single-Statement Scope Lifecycle

WITH high_salary AS (...)
CTE Created

── Executed by SELECT ──▶

Semicolon Suffix (;)
CTE Scope Immediately Ends

Recursive CTEs: Conceptual Preview

In advanced SQL, a Recursive CTE is a specialized CTE that references itself to iteratively traverse hierarchical trees:

CEO ➔ Manager ➔ Lead Engineer ➔ Junior Developer

(Recursive CTE syntax and mechanics will be explored in depth in a dedicated advanced module).

CTE vs. Temporary Table (CREATE TEMP TABLE)

Attribute	CTE (Common Table Expression)	Temporary Table (TEMP TABLE)
Scope	Single SQL statement only	Entire database user session
Storage	Inline query-scoped expression	Physical temporary database object
Indexing	Cannot be indexed directly	Can create custom indexes
Best Use Case	Modularizing single multi-step queries	Heavy multi-query analytical scripts

Common CTE Mistakes to Avoid

1. Repeating the 'WITH' Keyword

Writing WITH cte1 AS (...), WITH cte2 AS (...). Use WITH once, separated by commas.

2. Forgetting Parentheses

Omitting parentheses around the inner query causes a syntax error.

3. Querying CTE in a Subsequent Query

Attempting to query the CTE name after the terminating semicolon causes "relation does not exist".

4. Vague CTE Naming

Using generic names like t1 or temp instead of descriptive domain names like monthly_revenue.

Practical CTE Query Exercises

Exercise 1: Department Salary Rollup CTE

WITH dept_rollup AS (
    SELECT
        department,
        COUNT(*) AS headcount,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT *
FROM dept_rollup
WHERE avg_salary > 70000;

Exercise 2: Multi-Step Sales Spike Finder

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS sales_month,
        SUM(amount) AS total_revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
),
spike_months AS (
    SELECT *
    FROM monthly_sales
    WHERE total_revenue > 3000
)
SELECT *
FROM spike_months
ORDER BY total_revenue DESC;

⚡ Live Interactive SQL CTE Practice Lab

Construct CTEs step-by-step, convert messy subqueries, chain multiple pipelines, and test statement scope

Filter Threshold:

Generated SQL CTE

WITH high_salary AS (
  SELECT id, name, department, salary
  FROM employees
  WHERE salary > 70000
)
SELECT * FROM high_salary;

CTE Intermediate Output (3 records)Named: high_salary

id	Name	Department	Salary	Status
1	Rahul Sharma	Engineering	$95,000	✓ Captured in CTE
3	Priya Patel	Engineering	$110,000	✓ Captured in CTE
5	Rohan Gupta	Sales	$75,000	✓ Captured in CTE

SQL CTE Best Practices

Use Meaningful Domain Names: Name CTEs by their analytical purpose (e.g. monthly_revenue, not temp1).
Keep Each CTE Focused on One Step: Avoid creating a single giant 200-line CTE when two 20-line CTEs are much cleaner.
Refactor Deep Subqueries into CTEs: Improve code review readability for team data analytics workflows.
Remember Single-Statement Scope: Use Temporary Tables only if intermediate results must persist across multiple distinct queries.

What You Should Know Now: Checklist

✓Definition: CTEs assign a temporary name to a query result for use in the main query.
✓Syntax: WITH cte_name AS (...) SELECT ... FROM cte_name;
✓Multiple CTEs: Defined with a single WITH keyword and separated by commas.
✓Statement Scope: CTEs exist only for the execution lifetime of that single query.
✓CTE vs Subquery: CTEs organize multi-step transformations top-to-bottom rather than inside-out.

TEST YOUR KNOWLEDGE

Knowledge Assessment: SQL CTEs (Common Table Expressions)

Test your understanding of WITH syntax, statement scope, multiple chained CTEs, CTE vs Subqueries, and temporary table differences.

Question 1 of 8Current Score: 0 / 0

What does CTE stand for in relational SQL databases?