TECH **SQL for Data Analysis: Common Table Expressions (CTEs) – Single & Multiple**

SQL for Data Analysis: Common Table Expressions (CTEs) – Single & Multiple

Hyper-Practical, Zero-Fluff Study Guide

1. What This Is & Why It Matters

What CTEs are:
A Common Table Expression (CTE) is a temporary result set you define within a SQL query using the WITH clause. Think of it like a sticky note for your query—you write down a subquery once, give it a name, and reuse it multiple times in the same query. CTEs can be single (one temporary table) or multiple (chained together like Lego blocks).

Why this matters in production:
- Legacy codebases: You inherit a 500-line query with nested subqueries that look like spaghetti. CTEs let you refactor it into readable, modular blocks.
- Debugging: Instead of scrolling through a monolithic query, you can isolate and test each CTE independently.
- Performance: Some databases (like PostgreSQL) materialize CTEs, meaning they run once and cache the result—saving compute time.
- Collaboration: Your teammate can glance at a CTE named customer_churn_risk and instantly understand its purpose, unlike a cryptic subquery.

Real-world scenario:
You’re analyzing e-commerce data. Your boss asks: "Show me the top 10 products by revenue, but only for customers who made their first purchase in 2023 and have a lifetime value > $500." Without CTEs, you’d write a nested mess. With CTEs, you break it into: 1. first_time_customers_2023 (filter for first purchase year) 2. high_value_customers (join with lifetime value) 3. product_revenue (aggregate sales) Then join them cleanly.

2. Core Concepts & Components

• WITH clause
  The SQL keyword that starts a CTE. Example: sql WITH my_cte AS (SELECT * FROM users WHERE signup_date > '2023-01-01') Production insight: Always put the WITH clause before your main query (e.g., SELECT, INSERT).

• Single CTE
  One temporary table defined in a WITH clause.
  sql WITH active_users AS (
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id ) Production insight: Use descriptive names (e.g., active_users vs. cte1). Future you (or your teammate) will thank you.

• Multiple CTEs
  Chain multiple CTEs in one WITH clause, separated by commas.
  sql WITH active_users AS (...), high_value_users AS (
SELECT * FROM active_users WHERE order_count > 5 ) Production insight: Order matters! Later CTEs can reference earlier ones, but not vice versa.

• Recursive CTEs
  A CTE that references itself (e.g., for hierarchical data like org charts).
  sql WITH RECURSIVE org_hierarchy AS (
SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id FROM employees e
JOIN org_hierarchy oh ON e.manager_id = oh.id ) Production insight: Recursive CTEs are powerful but dangerous—they can cause infinite loops. Always include a termination condition (e.g., WHERE depth < 10).

• Materialization
  Some databases (PostgreSQL, SQL Server) cache CTE results, while others (MySQL < 8.0) re-run them every time they’re referenced.
  Production insight: If performance is critical, test whether your database materializes CTEs. If not, consider a temporary table instead.

• CTEs vs. Subqueries

• Subquery: Nested inside another query (harder to read, can’t be reused).

• CTE: Defined once, referenced multiple times (cleaner, modular).
  Production insight: Use CTEs for complex logic or repeated calculations. Use subqueries for simple, one-off filters.

• CTEs vs. Temporary Tables

• CTE: Exists only for the duration of the query.

• Temporary table: Persists for the session (useful for multi-step ETL).
  Production insight: Use CTEs for ad-hoc analysis. Use temp tables for batch processing (e.g., loading data into a dashboard).

• CTEs in Views
  You can use CTEs inside a CREATE VIEW statement to make complex logic reusable.
  sql CREATE VIEW customer_segmentation AS WITH high_value AS (...), at_risk AS (...) SELECT * FROM high_value UNION ALL SELECT * FROM at_risk; Production insight: Views with CTEs are self-documenting—great for sharing logic across teams.

3. Step-by-Step Hands-On: Writing Your First CTEs

Prerequisites

• A SQL environment (e.g., PostgreSQL, BigQuery, Snowflake, or SQLite).
• A sample dataset. We’ll use this schema: ```sql CREATE TABLE users (
  user_id INT,
  name VARCHAR(100),
  signup_date DATE );

CREATE TABLE orders (
order_id INT,
user_id INT,
order_date DATE,
amount DECIMAL(10,2) );

-- Insert sample data INSERT INTO users VALUES (1, 'Alice', '2023-01-15'), (2, 'Bob', '2022-11-03'), (3, 'Charlie', '2023-02-20');

INSERT INTO orders VALUES (101, 1, '2023-01-20', 50.00), (102, 1, '2023-02-10', 75.50), (103, 2, '2022-12-01', 30.00), (104, 3, '2023-03-05', 200.00); ```

Task: Find High-Value Users (LTV > $100) Who Signed Up in 2023

We’ll break this into 3 CTEs: 1. users_2023: Filter for 2023 signups.
2. user_lifetime_value: Calculate total spend per user.
3. high_value_users: Filter for LTV > $100.

Step 1: Write the first CTE (users_2023)

WITH users_2023 AS (
  SELECT user_id, name
  FROM users
  WHERE signup_date >= '2023-01-01'
)
SELECT * FROM users_2023;

Expected output:

user_id | name    |
--------|---------|
1       | Alice   |
3       | Charlie |

Step 2: Add the second CTE (user_lifetime_value)

WITH
users_2023 AS (
  SELECT user_id, name
  FROM users
  WHERE signup_date >= '2023-01-01'
),
user_lifetime_value AS (
  SELECT
    o.user_id,
    SUM(o.amount) AS total_spend
  FROM orders o
  GROUP BY o.user_id
)
SELECT * FROM user_lifetime_value;

Expected output:

user_id | total_spend |
--------|-------------|
1       | 125.50      |
2       | 30.00       |
3       | 200.00      |

Step 3: Add the third CTE (high_value_users) and final query

WITH
users_2023 AS (
  SELECT user_id, name
  FROM users
  WHERE signup_date >= '2023-01-01'
),
user_lifetime_value AS (
  SELECT
    o.user_id,
    SUM(o.amount) AS total_spend
  FROM orders o
  GROUP BY o.user_id
),
high_value_users AS (
  SELECT
    u.user_id,
    u.name,
    ulv.total_spend
  FROM users_2023 u
  JOIN user_lifetime_value ulv ON u.user_id = ulv.user_id
  WHERE ulv.total_spend > 100
)
SELECT * FROM high_value_users;

Expected output:

user_id | name    | total_spend |
--------|---------|-------------|
1       | Alice   | 125.50      |
3       | Charlie | 200.00      |

Step 4: Verify the result

• Alice and Charlie signed up in 2023 and have LTV > $100.
• Bob is excluded (signed up in 2022).
• The query is modular—you can test each CTE independently.

4. ? Production-Ready Best Practices

Readability & Maintainability

• Name CTEs like variables: Use snake_case and describe the purpose (e.g., active_users_last_30_days).
• Limit CTEs to 10–15 lines: If a CTE grows too large, split it into smaller CTEs or use a temporary table.
• Order CTEs logically: Earlier CTEs should feed into later ones. Avoid circular references.
• Comment complex logic: Add -- This CTE calculates 30-day rolling revenue above tricky CTEs.

Performance

• Avoid SELECT *: Explicitly list columns to reduce memory usage.
• Filter early: Push WHERE clauses into CTEs to reduce data volume early.
  ```sql -- Bad: Filter after joining WITH all_orders AS (SELECT * FROM orders) SELECT * FROM all_orders WHERE order_date > '2023-01-01';

-- Good: Filter in the CTE WITH recent_orders AS (
SELECT * FROM orders WHERE order_date > '2023-01-01' ) SELECT * FROM recent_orders; - Test materialization: In PostgreSQL, use `EXPLAIN ANALYZE` to check if CTEs are materialized.sql EXPLAIN ANALYZE WITH cte AS (SELECT * FROM users) SELECT * FROM cte; `` Look forCTE Scan(materialized) vs.Subquery Scan` (not materialized).

Debugging

• Isolate CTEs: Run each CTE as a standalone query to verify intermediate results.
• Use LIMIT: Test with a small dataset first.
  sql WITH test_cte AS (SELECT * FROM huge_table LIMIT 100) SELECT * FROM test_cte;
• Check for duplicates: Use DISTINCT or GROUP BY if CTEs produce unexpected rows.

Collaboration

• Document assumptions: Add comments for business logic (e.g., -- LTV = sum(order_amount) * 0.8 (20% churn rate)).
• Version control: Store SQL scripts in Git with clear commit messages (e.g., Add CTE for customer segmentation).

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Syntax questions:
   "Which of the following is the correct way to define a CTE?"
2. ❌ SELECT * FROM (WITH cte AS (SELECT * FROM users) SELECT * FROM cte);

3. ✅ WITH cte AS (SELECT * FROM users) SELECT * FROM cte;

4. Performance questions:
   "You have a query with 3 CTEs. The first CTE filters a large table. Where should you add a WHERE clause to optimize performance?"

5. ✅ Inside the first CTE (to reduce data early).

6. Recursive CTEs:
   "Which keyword is required for a recursive CTE?"

7. ✅ WITH RECURSIVE (not just WITH).

8. CTEs vs. subqueries:
   "When should you use a CTE instead of a subquery?"

9. ✅ When the logic is complex or reused multiple times in the query.

Key ⚠️ Trap Distinctions

• CTEs vs. Temp Tables:
• CTEs: Ephemeral (exist only for the query).
• Temp tables: Persistent (exist for the session).
• Materialization:
• PostgreSQL/SQL Server: Materializes CTEs by default (caches results).
• MySQL < 8.0: Does not materialize (re-runs CTEs every time).
• Column aliases:
• CTEs must have unique column names if referenced later.
• Example of a mistake:
  sql
WITH cte AS (SELECT user_id, user_id FROM users) -- Error: duplicate column

Common Scenario-Based Question

"You need to calculate the 7-day rolling average of daily sales. Which approach is most efficient?" - ❌ Subquery in SELECT (recalculates for every row).
- ❌ Temporary table (overkill for a single query).
- ✅ CTE (clean, modular, and can be materialized).

7. ? Hands-On Challenge

Challenge:
Using the users and orders tables from earlier, write a query that: 1. Finds users who made more than 1 order in 2023.
2. Calculates their average order value.
3. Returns the results sorted by average order value (highest first).

Solution:

WITH
users_2023 AS (
  SELECT user_id, name
  FROM users
  WHERE signup_date >= '2023-01-01'
),
user_order_counts AS (
  SELECT
    o.user_id,
    COUNT(*) AS order_count,
    AVG(o.amount) AS avg_order_value
  FROM orders o
  JOIN users_2023 u ON o.user_id = u.user_id
  WHERE o.order_date >= '2023-01-01'
  GROUP BY o.user_id
  HAVING COUNT(*) > 1
)
SELECT * FROM user_order_counts
ORDER BY avg_order_value DESC;

Why it works:
- users_2023 filters for 2023 signups.
- user_order_counts joins with orders, groups by user, and filters for >1 order.
- The final query sorts by avg_order_value.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL CTE Documentation – Official guide with examples.
2. SQLZoo CTE Tutorial – Interactive exercises.
3. Mode Analytics SQL Tutorial – Real-world SQL patterns (including CTEs).
4. "SQL for Data Analysis" (O’Reilly) – Chapter 5 covers CTEs in depth.