Cte

Chapter 17: Common Table Expressions (CTEs)

Advanced Query Techniques with CTEs

---

17.1 CTE Fundamentals

Common Table Expressions (CTEs) provide powerful ways to organize complex queries, making them more readable and maintainable.

                    CTE vs Subqueries vs Temporary Tables
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │  CTE (WITH clause)                                                │
    │  • Temporary named result set                                     │
    │  • Exists only for single query                                    │
    │  • Improves readability                                            │
    │  • Supports recursion                                              │
    │  • Can reference itself                                            │
    ├─────────────────────────────────────────────────────────────────────┤
    │  Subquery                                                          │
    │  • Nested in FROM, WHERE, or SELECT                               │
    │  • Can't be referenced multiple times                             │
    │  • Less readable for complex queries                              │
    ├─────────────────────────────────────────────────────────────────────┤
    │  Temporary Table                                                   │
    │  • Persists for session/transaction                               │
    │  • Can be indexed                                                  │
    │  • Requires explicit creation and cleanup                         │
    │  • Useful for large data transformations                          │
    └─────────────────────────────────────────────────────────────────────┘

---

17.2 Basic CTE Syntax

-- Basic CTE
WITH user_orders AS (
    SELECT
        user_id,
        COUNT(*) AS order_count,
        SUM(total) AS total_spent
    FROM orders
    GROUP BY user_id
)
SELECT
    u.username,
    u.email,
    COALESCE(uo.order_count, 0) AS order_count,
    COALESCE(uo.total_spent, 0) AS total_spent
FROM users u
LEFT JOIN user_orders uo ON u.id = uo.user_id;

-- Multiple CTEs in one query
WITH
    active_users AS (
        SELECT id, username FROM users WHERE is_active = TRUE
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) AS cnt FROM orders GROUP BY user_id
    )
SELECT
    a.username,
    COALESCE(uo.cnt, 0) AS order_count
FROM active_users a
LEFT JOIN user_orders uo ON a.id = uo.user_id;

-- CTE with WHERE clause
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT
    u.username,
    ro.*
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;

---

17.3 Recursive CTEs

Recursion allows CTEs to reference themselves, enabling hierarchical data processing.

-- Basic recursive CTE: Numbers 1-10
WITH RECURSIVE counter(n) AS (
    -- Base case: Start at 1
    SELECT 1

    UNION ALL

    -- Recursive case: Increment
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

-- Hierarchical data: Organizational chart
WITH RECURSIVE org_chart AS (
    -- Base case: Top-level employees (no manager)
    SELECT
        id,
        name,
        manager_id,
        1 AS level,
        name AS path
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: Employees with managers
    SELECT
        e.id,
        e.name,
        e.manager_id,
        oc.level + 1,
        oc.path || ' > ' || e.name
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Recursive CTE: Find all subordinates
WITH RECURSIVE subordinates AS (
    -- Start with direct reports
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id = 5

    UNION ALL

    -- Find subordinates of each employee
    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
    WHERE s.depth < 5  -- Limit recursion depth
)
SELECT DISTINCT id, name, depth FROM subordinates;

-- Recursive CTE: Date range
WITH RECURSIVE date_series AS (
    SELECT '2024-01-01'::date AS date

    UNION ALL

    SELECT date + 1 FROM date_series
    WHERE date < '2024-01-31'
)
SELECT date FROM date_series;

---

17.4 CTE for Data Transformation

-- Data transformation with CTE
WITH
    raw_data AS (
        -- Get raw order data
        SELECT
            o.id,
            o.user_id,
            u.username,
            o.total,
            o.status,
            o.created_at
        FROM orders o
        JOIN users u ON o.user_id = u.id
    ),
    aggregated AS (
        -- Aggregate by user
        SELECT
            user_id,
            username,
            COUNT(*) AS total_orders,
            SUM(total) AS total_spent,
            AVG(total) AS avg_order_value,
            MIN(created_at) AS first_order,
            MAX(created_at) AS last_order
        FROM raw_data
        GROUP BY user_id, username
    ),
    ranked AS (
        -- Rank by spending
        SELECT
            *,
            RANK() OVER (ORDER BY total_spent DESC) AS spending_rank
        FROM aggregated
    )
SELECT
    spending_rank,
    username,
    total_orders,
    total_spent,
    avg_order_value
FROM ranked
WHERE spending_rank <= 10;

-- Data cleansing with CTE
WITH
    clean_orders AS (
        SELECT
            id,
            user_id,
            TRIM(status) AS status,  -- Remove whitespace
            COALESCE(total, 0) AS total,  -- Replace NULL with 0
            created_at
        FROM orders
    ),
    valid_orders AS (
        SELECT * FROM clean_orders
        WHERE status IN ('pending', 'completed', 'cancelled')
        AND total > 0
    )
SELECT * FROM valid_orders;

---

17.5 CTE for Complex Queries

-- Multiple operations in one query
WITH
    category_sales AS (
        SELECT
            p.category_id,
            SUM(o.quantity) AS units_sold,
            SUM(o.quantity * p.price) AS revenue
        FROM order_items o
        JOIN products p ON o.product_id = p.id
        GROUP BY p.category_id
    ),
    category_avg AS (
        SELECT AVG(revenue) AS avg_revenue FROM category_sales
    )
SELECT
    c.name AS category,
    cs.units_sold,
    cs.revenue,
    CASE
        WHEN cs.revenue > ca.avg_revenue THEN 'Above Average'
        ELSE 'Below Average'
    END AS performance
FROM category_sales cs
JOIN categories c ON cs.category_id = c.id
CROSS JOIN category_avg ca;

-- CTE with conditional logic
WITH
    user_stats AS (
        SELECT
            user_id,
            COUNT(*) AS order_count,
            SUM(total) AS total_spent
        FROM orders
        GROUP BY user_id
    )
SELECT
    u.username,
    COALESCE(us.order_count, 0) AS orders,
    COALESCE(us.total_spent, 0) AS spent,
    CASE
        WHEN us.total_spent > 1000 THEN 'VIP'
        WHEN us.total_spent > 500 THEN 'Premium'
        WHEN us.total_spent > 0 THEN 'Regular'
        ELSE 'New'
    END AS customer_tier
FROM users u
LEFT JOIN user_stats us ON u.id = us.user_id;

---

17.6 CTE for Pagination

-- Keyset pagination using CTE
WITH ordered_orders AS (
    SELECT
        id,
        user_id,
        total,
        created_at,
        ROW_NUMBER() OVER (ORDER BY created_at DESC, id DESC) AS row_num
    FROM orders
)
SELECT * FROM ordered_orders
WHERE row_num > 10 AND row_num <= 20;

-- Efficient pagination for large tables
WITH paginated AS (
    SELECT * FROM orders
    WHERE (created_at, id) < (LAST_VALUE, LAST_ID)
    ORDER BY created_at DESC, id DESC
    LIMIT 20
)
SELECT * FROM paginated;

-- CTE for page info
WITH
    order_counts AS (
        SELECT COUNT(*) AS total FROM orders
    ),
    page_data AS (
        SELECT
            o.*,
            ROW_NUMBER() OVER (ORDER BY created_at DESC) AS row_num
        FROM orders o
    )
SELECT
    pd.*,
    oc.total,
    CEIL(oc.total::numeric / 20) AS total_pages
FROM page_data pd
CROSS JOIN order_counts oc
WHERE pd.row_num BETWEEN 1 AND 20;

---

17.7 CTE vs Temporary Tables

-- Using CTE (ephemeral)
WITH temp_data AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
)
SELECT * FROM temp_data WHERE status = 'completed';

-- Using temporary table (persistent within session)
CREATE TEMP TABLE temp_orders AS
SELECT * FROM orders WHERE created_at > '2024-01-01';

SELECT * FROM temp_orders WHERE status = 'completed';

-- When to use CTE:
-- • Query needs temporary result once
-- • Improves readability
-- • Recursion needed
-- • No need for indexes on intermediate result

-- When to use temp table:
-- • Multiple queries need the same data
-- • Large dataset requires indexing
-- • Complex transformations need multiple passes
-- • Data needs to be modified between queries

---

17.8 Nested CTEs

-- Nested CTE structure
WITH
    outer_cte AS (
        WITH
            inner_cte AS (
                SELECT * FROM base_table
            )
        SELECT * FROM inner_cte
    )
SELECT * FROM outer_cte;

-- Cleaner: Use multiple CTEs at same level
WITH
    inner_cte AS (
        SELECT * FROM base_table
    ),
    outer_cte AS (
        SELECT * FROM inner_cte WHERE condition
    )
SELECT * FROM outer_cte;

-- Complex nested example
WITH
    step1 AS (
        SELECT user_id, SUM(total) AS total FROM orders GROUP BY user_id
    ),
    step2 AS (
        SELECT
            s1.*,
            u.username,
            RANK() OVER (ORDER BY s1.total DESC) AS rank
        FROM step1 s1
        JOIN users u ON s1.user_id = u.id
    ),
    step3 AS (
        SELECT
            *,
            CASE
                WHEN rank <= 10 THEN 'Top 10'
                WHEN rank <= 100 THEN 'Top 100'
                ELSE 'Other'
            END AS tier
        FROM step2
    )
SELECT * FROM step3 WHERE tier = 'Top 10';

---

17.9 CTE with Modification (PostgreSQL 12+)

-- CTE with UPDATE
WITH updated_orders AS (
    UPDATE orders
    SET status = 'processed'
    WHERE status = 'pending'
    RETURNING id, user_id, total
)
SELECT * FROM updated_orders;

-- CTE with INSERT
WITH new_products AS (
    INSERT INTO products (name, price)
    VALUES ('New Product', 99.99)
    RETURNING id, name, price
)
SELECT np.*, c.name AS category
FROM new_products np
JOIN categories c ON c.id = 1;

-- CTE with DELETE
WITH deleted_orders AS (
    DELETE FROM orders
    WHERE created_at < '2023-01-01'
    RETURNING id, user_id, total
)
SELECT
    user_id,
    COUNT(*) AS orders_deleted,
    SUM(total) AS total_refunded
FROM deleted_orders
GROUP BY user_id;

---

17.10 Best Practices

                    CTE Best Practices
    ========================================================================

    1. Use CTEs for Readability
       ┌──────────────────────────────────────────────────────────────┐
       │  • Break complex queries into logical steps                │
       │  • Name CTEs descriptively                                  │
       │  • Order CTEs in dependency order                          │
       └──────────────────────────────────────────────────────────────┘

    2. Use Recursive CTEs for Hierarchies
       ┌──────────────────────────────────────────────────────────────┐
       │  • Perfect for org charts, category trees                   │
       │  • Always include termination condition                     │
       │  • Consider MAX RECURSION DEPTH to prevent infinite loops  │
       └──────────────────────────────────────────────────────────────┘

    3. Consider Performance
       ┌──────────────────────────────────────────────────────────────┐
       │  • PostgreSQL may materialize CTEs                         │
       │  • For repeated use, consider temp table                   │
       │  • Use EXPLAIN ANALYZE to check performance                │
       └──────────────────────────────────────────────────────────────┘

    4. Multiple CTEs
       ┌──────────────────────────────────────────────────────────────┐
       │  • Define independent CTEs first                            │
       │  • Reference earlier CTEs in later ones                    │
       │  • Use meaningful names                                     │
       └──────────────────────────────────────────────────────────────┘

    5. Use with Modification Statements
       ┌──────────────────────────────────────────────────────────────┐
       │  • Get affected rows from UPDATE/INSERT/DELETE              │
       │  • Useful for audit trails                                  │
       │  • Works with RETURNING clause                               │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 18: GROUP BY & Aggregation

---

Last Updated: February 2026