CTEs With

Why this matters

Common Table Expressions (CTEs, introduced with the WITH keyword) make complex queries readable and maintainable. As a Data Analyst, you’ll use CTEs to: clean raw data before aggregations, build step-by-step transformations, create date spines to fill missing days, and compare intermediary results quickly without creating permanent objects.

• Build layered analytics (filter → dedupe → aggregate) in one query.
• Reuse intermediate logic across multiple joins.
• Create recursive sequences (dates, hierarchies) when needed.

Concept explained simply

A CTE is a named result set you define right before your main SELECT. Think of it like a temporary, readable subquery. It only exists for that single statement and helps you break a big problem into steps.

Mental model

• Recipe steps: each CTE is a step; the final SELECT is the plated dish.
• Scope: CTEs exist only for the statement that follows the WITH block.
• Order: later CTEs can reference earlier ones; the final SELECT can reference any CTE defined above it.

Syntax

WITH cte_name [ (optional_column_list) ] AS (
  -- any SELECT query
  SELECT ...
), another_cte AS (
  SELECT ... FROM cte_name
)
SELECT ...
FROM another_cte;

• Separate multiple CTEs with commas.
• Optional column list must match the number of columns in the CTE SELECT.
• Some SQL engines require a semicolon before WITH if it’s not the first statement in the script.

Worked examples

WITH recent_orders AS (
  SELECT o.order_id, o.customer_id, o.order_date, o.total_amount
  FROM orders o
  WHERE o.status = 'completed'
    AND o.order_date >= CURRENT_DATE - INTERVAL '90 day'
), ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM recent_orders
)
SELECT customer_id, order_id, order_date, total_amount
FROM ranked
WHERE rn = 1;

WITH gross AS (
  SELECT DATE_TRUNC('month', o.order_date) AS month,
         SUM(oi.quantity * oi.unit_price) AS gross_revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.order_id
  WHERE o.status = 'completed'
  GROUP BY 1
), refunds AS (
  SELECT DATE_TRUNC('month', o.order_date) AS month,
         COALESCE(SUM(r.refund_amount), 0) AS refund_amount
  FROM orders o
  JOIN refunds r ON r.order_id = o.order_id
  GROUP BY 1
)
SELECT g.month,
       g.gross_revenue - COALESCE(r.refund_amount, 0) AS net_revenue
FROM gross g
LEFT JOIN refunds r USING (month)
ORDER BY g.month;

WITH RECURSIVE months AS (
  SELECT DATE_TRUNC('month', DATE '2023-01-01') AS month
  UNION ALL
  SELECT month + INTERVAL '1 month'
  FROM months
  WHERE month < DATE_TRUNC('month', CURRENT_DATE)
), revenue AS (
  SELECT DATE_TRUNC('month', o.order_date) AS month,
         SUM(o.total_amount) AS revenue
  FROM orders o
  WHERE o.status = 'completed'
  GROUP BY 1
)
SELECT m.month, COALESCE(r.revenue, 0) AS revenue
FROM months m
LEFT JOIN revenue r USING (month)
ORDER BY m.month;

Who this is for

• Aspiring and junior Data Analysts wanting cleaner, faster SQL.
• Analysts refactoring nested subqueries into readable steps.
• Anyone preparing for SQL interviews with practical patterns.

Prerequisites

• Comfort with SELECT, WHERE, GROUP BY, JOIN.
• Basic window functions (ROW_NUMBER, RANK) are helpful.
• Familiarity with date functions and aggregation.

Learning path

1. Write a single CTE to encapsulate a filter.
2. Chain multiple CTEs (2–3) to build logic in steps.
3. Add window functions inside a CTE for dedup and ranking.
4. Use a recursive CTE for a date spine or simple hierarchy.
5. Refactor a long query into CTEs and compare readability and performance.

Exercises

These mirror the tasks below. Complete them in your SQL environment. Progress is available to everyone; saving your progress requires logging in.

1. Exercise 1: Latest completed order per customer in the last 60 days using a CTE and ROW_NUMBER. Target columns: customer_id, order_id, order_date, total_amount.
2. Exercise 2: Top 3 products by monthly revenue. Use one CTE to compute product revenue by month, a second CTE to rank per month, then select rank ≤ 3.
3. Exercise 3: Build a daily date spine for the last 30 days (recursive CTE), left join to daily orders to fill gaps with zeros.

Common mistakes and self-checks

• Mistake: Trailing comma after the last CTE. Self-check: Ensure only CTE separators use commas; the last CTE is followed by the final SELECT.
• Mistake: Column list count mismatch. Self-check: If you specify (col1, col2), your SELECT must return exactly 2 columns in that order.
• Mistake: Assuming materialization. Self-check: Many engines inline CTEs; if reused many times and heavy, consider temp tables for performance.
• Mistake: Referencing CTEs in the wrong order. Self-check: Only reference CTEs defined earlier in the WITH block.
• Mistake: Infinite recursion. Self-check: In recursive CTEs, ensure a termination condition (e.g., date <= end_date).

Practical projects

• Marketing funnel: Build stepwise CTEs for sessions → signups → purchases by week. Output conversion rates.
• Deduplicated customer 360: Use CTEs to standardize country names, pick latest profile per customer, then join to orders.
• Revenue assurance: Create a date spine, compare expected daily revenue to actuals, and flag anomalies.

Next steps

• Refactor one of your longest nested queries into 2–4 CTEs with clear names.
• Add a recursive CTE to generate a calendar or hierarchy for a current project.
• Take the Quick Test below. Your answers are checked for everyone; log in to save your progress.

Mini challenge

Using two or more CTEs, compute weekly active customers (placed at least one completed order in a week) and then calculate WoW change percentage. Keep names clear and steps short.