Master Subqueries and CTEs: Advanced SQL for Complex Analytics

Picture this: You're analyzing customer behavior for an e-commerce platform, and your stakeholder asks a seemingly simple question: "Which customers made purchases above the average order value, and what's the distribution of their subsequent orders?" Your first instinct might be to export data to Excel or write multiple queries. But seasoned SQL practitioners know there's a more elegant path—one that leverages the true power of subqueries and Common Table Expressions (CTEs) to solve complex analytical problems within the database itself.

This isn't just about writing cleaner code (though you will). It's about unlocking SQL's capacity for sophisticated data analysis while maintaining query performance at scale. By the end of this lesson, you'll transform from someone who writes basic SELECT statements into a practitioner who can architect complex analytical queries that would make database administrators nod in approval.

The techniques we'll explore here represent the difference between junior and senior-level SQL competency. While basic queries get you answers, subqueries and CTEs get you insights—and they do it in ways that are maintainable, readable, and performant.

What you'll learn:

• How to architect complex analytical queries using nested subqueries and their performance implications
• When and how to replace subqueries with CTEs for improved readability and maintainability
• Advanced CTE patterns including recursive queries for hierarchical data analysis
• Performance optimization techniques specific to subqueries and CTEs
• Query execution plan analysis and optimization strategies for complex nested queries
• Real-world patterns for window functions combined with CTEs for advanced analytics

Prerequisites

You should be comfortable with:

• Writing multi-table JOINs
• Using GROUP BY and aggregate functions
• Basic understanding of query execution order
• Familiarity with window functions (ROW_NUMBER, RANK, LAG/LEAD)

Understanding Subqueries: The Foundation of Complex Analysis

Subqueries are queries nested within other queries, and they're far more nuanced than most practitioners realize. Let's start with a realistic scenario using an e-commerce database to understand when and why you'd use them.

Consider this customer analytics challenge: identify customers whose lifetime value exceeds the average, but only consider customers who've made at least 3 purchases in the last year. This requires multiple levels of aggregation and filtering—perfect for subqueries.

-- First, let's see the naive approach that won't work
SELECT 
    customer_id,
    SUM(order_total) as lifetime_value
FROM orders 
WHERE SUM(order_total) > (SELECT AVG(total_spend) FROM customer_summary)
  AND customer_id IN (SELECT customer_id FROM recent_active_customers);
-- ERROR: Can't use aggregate in WHERE clause

This fails because you can't use aggregates directly in WHERE clauses without GROUP BY. Here's how subqueries solve this elegantly:

-- Proper subquery approach
SELECT 
    customer_id,
    lifetime_value,
    order_count
FROM (
    -- Subquery calculates per-customer metrics
    SELECT 
        customer_id,
        SUM(order_total) as lifetime_value,
        COUNT(*) as order_count,
        MAX(order_date) as last_order_date
    FROM orders 
    WHERE order_date >= DATEADD(year, -1, CURRENT_DATE)
    GROUP BY customer_id
    HAVING COUNT(*) >= 3  -- At least 3 orders
) customer_metrics
WHERE lifetime_value > (
    -- Correlated subquery for dynamic threshold
    SELECT AVG(SUM(order_total))
    FROM orders o2
    WHERE o2.order_date >= DATEADD(year, -1, CURRENT_DATE)
    GROUP BY o2.customer_id
);

Types of Subqueries and Their Use Cases

Understanding the different types of subqueries is crucial for choosing the right approach:

Scalar Subqueries return a single value and are often used in SELECT clauses or WHERE conditions:

-- Adding market context to each order
SELECT 
    order_id,
    order_total,
    order_total - (SELECT AVG(order_total) FROM orders) as deviation_from_avg,
    (order_total / (SELECT MAX(order_total) FROM orders)) * 100 as pct_of_max_order
FROM orders 
WHERE order_date = CURRENT_DATE;

Correlated Subqueries reference columns from the outer query, executing once per outer row. They're powerful but can be performance-intensive:

-- Find customers whose latest order was above their personal average
SELECT DISTINCT 
    o1.customer_id,
    o1.order_total as latest_order_total,
    (SELECT AVG(order_total) 
     FROM orders o2 
     WHERE o2.customer_id = o1.customer_id) as personal_avg
FROM orders o1
WHERE o1.order_date = (
    SELECT MAX(order_date) 
    FROM orders o2 
    WHERE o2.customer_id = o1.customer_id
)
AND o1.order_total > (
    SELECT AVG(order_total) 
    FROM orders o3 
    WHERE o3.customer_id = o1.customer_id
);

Exists/Not Exists Subqueries check for the existence of rows meeting certain conditions:

-- Find products that have never been returned
SELECT 
    product_id,
    product_name,
    category
FROM products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM returns r 
    JOIN order_items oi ON r.order_item_id = oi.order_item_id
    WHERE oi.product_id = p.product_id
);

Subquery Performance Considerations

The biggest mistake with subqueries is not understanding their execution patterns. Let's analyze query execution plans to understand the performance implications:

-- This correlated subquery executes N times (once per outer row)
EXPLAIN ANALYZE
SELECT 
    customer_id,
    order_total,
    (SELECT COUNT(*) 
     FROM orders o2 
     WHERE o2.customer_id = o1.customer_id 
       AND o2.order_date < o1.order_date) as previous_orders
FROM orders o1
WHERE order_date >= '2023-01-01';

When you run this with EXPLAIN ANALYZE, you'll see something like:

Nested Loop  (cost=0.00..45000.00 rows=1000 loops=1000)
  ->  Seq Scan on orders o1  (cost=0.00..450.00 rows=1000)
  ->  Aggregate  (cost=0.43..0.44 rows=1)
    ->  Index Scan on orders o2  (cost=0.43..0.43 rows=1 loops=1000)

Notice the "loops=1000"—that inner query runs 1000 times! Here's a more efficient approach using window functions:

-- Window function alternative - single pass through data
SELECT 
    customer_id,
    order_total,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) - 1 as previous_orders
FROM orders
WHERE order_date >= '2023-01-01';

This pattern—recognizing when window functions can replace correlated subqueries—is a hallmark of expert-level SQL optimization.

Common Table Expressions: Readable Complex Queries

CTEs transform complex queries from write-once nightmares into maintainable, readable code. More importantly, they enable query patterns that would be nearly impossible with traditional subqueries.

Let's solve a real analytics challenge: calculate customer cohort retention rates by month. This requires multiple levels of aggregation and complex date logic:

-- Without CTEs: nested subquery hell
SELECT 
    cohort_month,
    retention_month,
    retained_customers,
    cohort_size,
    retention_rate
FROM (
    SELECT 
        cohort_month,
        retention_month,
        COUNT(DISTINCT customer_id) as retained_customers,
        FIRST_VALUE(cohort_size) OVER (PARTITION BY cohort_month ORDER BY retention_month) as cohort_size,
        COUNT(DISTINCT customer_id) * 100.0 / 
            FIRST_VALUE(cohort_size) OVER (PARTITION BY cohort_month ORDER BY retention_month) as retention_rate
    FROM (
        SELECT 
            customer_id,
            cohort_month,
            DATE_TRUNC('month', order_date) as retention_month,
            cohort_size
        FROM (
            SELECT 
                o.customer_id,
                o.order_date,
                c.first_order_month as cohort_month,
                c.cohort_size
            FROM orders o
            JOIN (
                SELECT 
                    customer_id,
                    DATE_TRUNC('month', MIN(order_date)) as first_order_month,
                    COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', MIN(order_date))) as cohort_size
                FROM orders
                GROUP BY customer_id
            ) c ON o.customer_id = c.customer_id
        ) customer_cohorts
    ) retention_data
    GROUP BY cohort_month, retention_month, cohort_size
) final_retention;
-- This is unreadable and unmaintainable

Now with CTEs:

-- CTE approach: clear, logical flow
WITH customer_cohorts AS (
    -- Define each customer's cohort (first purchase month)
    SELECT 
        customer_id,
        DATE_TRUNC('month', MIN(order_date)) as cohort_month
    FROM orders
    GROUP BY customer_id
),

cohort_sizes AS (
    -- Count customers in each cohort
    SELECT 
        cohort_month,
        COUNT(*) as cohort_size
    FROM customer_cohorts
    GROUP BY cohort_month
),

customer_orders AS (
    -- Add cohort info to all orders
    SELECT 
        o.customer_id,
        o.order_date,
        cc.cohort_month,
        DATE_TRUNC('month', o.order_date) as order_month
    FROM orders o
    JOIN customer_cohorts cc ON o.customer_id = cc.customer_id
),

retention_data AS (
    -- Calculate months since cohort for each order
    SELECT 
        cohort_month,
        order_month,
        customer_id,
        EXTRACT(EPOCH FROM (order_month - cohort_month)) / (30.44 * 24 * 3600) as months_since_cohort
    FROM customer_orders
)

-- Final retention calculation
SELECT 
    rd.cohort_month,
    CAST(rd.months_since_cohort AS INTEGER) as retention_month,
    COUNT(DISTINCT rd.customer_id) as retained_customers,
    cs.cohort_size,
    ROUND(
        COUNT(DISTINCT rd.customer_id) * 100.0 / cs.cohort_size, 
        2
    ) as retention_rate
FROM retention_data rd
JOIN cohort_sizes cs ON rd.cohort_month = cs.cohort_month
GROUP BY rd.cohort_month, rd.months_since_cohort, cs.cohort_size
ORDER BY rd.cohort_month, retention_month;

The CTE version is not just more readable—it's also more maintainable and debuggable. You can easily test each CTE independently or add intermediate results to understand data flow.

Advanced CTE Patterns

Multiple CTE References: CTEs can reference other CTEs, enabling complex data pipeline construction:

WITH daily_sales AS (
    SELECT 
        DATE_TRUNC('day', order_date) as sale_date,
        SUM(order_total) as daily_revenue,
        COUNT(DISTINCT customer_id) as unique_customers
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY DATE_TRUNC('day', order_date)
),

sales_with_moving_avg AS (
    SELECT 
        sale_date,
        daily_revenue,
        unique_customers,
        AVG(daily_revenue) OVER (
            ORDER BY sale_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as seven_day_avg_revenue
    FROM daily_sales
),

sales_trends AS (
    SELECT 
        *,
        daily_revenue - seven_day_avg_revenue as revenue_deviation,
        CASE 
            WHEN daily_revenue > seven_day_avg_revenue * 1.15 THEN 'High'
            WHEN daily_revenue < seven_day_avg_revenue * 0.85 THEN 'Low'
            ELSE 'Normal'
        END as performance_category
    FROM sales_with_moving_avg
)

SELECT 
    sale_date,
    daily_revenue,
    seven_day_avg_revenue,
    performance_category,
    -- Flag consecutive high/low performance days
    SUM(CASE WHEN performance_category = 'High' THEN 1 ELSE 0 END) 
        OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as high_streak,
    SUM(CASE WHEN performance_category = 'Low' THEN 1 ELSE 0 END) 
        OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as low_streak
FROM sales_trends
ORDER BY sale_date;

CTE Query Optimization: Modern query optimizers can often "inline" simple CTEs, but complex CTEs might be materialized. Understanding this helps with performance tuning:

-- This CTE will likely be inlined (simple aggregation)
WITH simple_totals AS (
    SELECT customer_id, SUM(order_total) as total_spend
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM simple_totals WHERE total_spend > 1000;

-- This CTE might be materialized (complex logic)
WITH complex_analytics AS (
    SELECT 
        customer_id,
        order_date,
        order_total,
        LAG(order_total, 1) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_order,
        LEAD(order_total, 1) OVER (PARTITION BY customer_id ORDER BY order_date) as next_order,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as order_sequence
    FROM orders
    WHERE order_date >= '2023-01-01'
)
SELECT * FROM complex_analytics WHERE order_sequence <= 5;

Use EXPLAIN ANALYZE to see whether your CTEs are inlined or materialized, and adjust complexity accordingly.

Recursive CTEs: Navigating Hierarchical Data

Recursive CTEs unlock one of SQL's most powerful capabilities: processing hierarchical data structures like organizational charts, category trees, or network relationships. Let's explore this with a realistic organizational hierarchy analysis.

Consider an employee table with manager relationships. We want to calculate the complete reporting structure, including spans of control and organizational depth:

-- Sample hierarchical data structure
CREATE TEMP TABLE employees AS
SELECT * FROM VALUES
(1, 'Sarah Chen', 'CEO', NULL, 250000),
(2, 'Mike Rodriguez', 'VP Engineering', 1, 180000),
(3, 'Lisa Zhang', 'VP Sales', 1, 175000),
(4, 'James Wilson', 'Engineering Manager', 2, 140000),
(5, 'Maria Garcia', 'Senior Developer', 4, 120000),
(6, 'David Kim', 'Developer', 4, 95000),
(7, 'Amy Thompson', 'Sales Manager', 3, 130000),
(8, 'John Davis', 'Sales Rep', 7, 85000),
(9, 'Emma Johnson', 'Sales Rep', 7, 90000),
(10, 'Alex Brown', 'VP Marketing', 1, 170000),
(11, 'Sophie Miller', 'Marketing Manager', 10, 125000)
AS t(employee_id, name, title, manager_id, salary);

Now let's build a recursive CTE to analyze this hierarchy:

WITH RECURSIVE org_hierarchy AS (
    -- Base case: top-level managers (no manager)
    SELECT 
        employee_id,
        name,
        title,
        manager_id,
        salary,
        0 as level,
        name as path,
        employee_id::text as id_path,
        1 as employee_count
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: employees with managers
    SELECT 
        e.employee_id,
        e.name,
        e.title,
        e.manager_id,
        e.salary,
        oh.level + 1,
        oh.path || ' > ' || e.name as path,
        oh.id_path || '>' || e.employee_id::text as id_path,
        1 as employee_count
    FROM employees e
    JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
),

hierarchy_analytics AS (
    SELECT 
        *,
        -- Count direct reports for each employee
        (SELECT COUNT(*) 
         FROM employees e2 
         WHERE e2.manager_id = org_hierarchy.employee_id) as direct_reports,
        
        -- Calculate total compensation for each subtree
        (WITH RECURSIVE subtree AS (
            SELECT employee_id, salary FROM employees WHERE employee_id = org_hierarchy.employee_id
            UNION ALL
            SELECT e.employee_id, e.salary 
            FROM employees e 
            JOIN subtree s ON e.manager_id = s.employee_id
         )
         SELECT SUM(salary) FROM subtree) as subtree_compensation
    FROM org_hierarchy
)

SELECT 
    REPEAT('  ', level) || name as indented_name,
    title,
    level as org_level,
    direct_reports,
    salary,
    subtree_compensation,
    ROUND(salary::numeric / NULLIF(subtree_compensation, 0) * 100, 1) as pct_of_subtree_comp,
    path
FROM hierarchy_analytics
ORDER BY id_path;

This query produces a complete organizational analysis:

    indented_name     |      title       | org_level | direct_reports | salary  | subtree_compensation
Sarah Chen           | CEO              |         0 |              3 | 250000  |              1565000
  Mike Rodriguez     | VP Engineering   |         1 |              1 | 180000  |               535000
    James Wilson     | Engineering Man. |         2 |              2 | 140000  |               355000
      Maria Garcia   | Senior Developer |         3 |              0 | 120000  |               120000
      David Kim      | Developer        |         3 |              0 |  95000  |                95000
  Lisa Zhang         | VP Sales         |         1 |              1 | 175000  |               480000
    Amy Thompson     | Sales Manager    |         2 |              2 | 130000  |               305000
      John Davis     | Sales Rep        |         3 |              0 |  85000  |                85000
      Emma Johnson   | Sales Rep        |         3 |              0 |  90000  |                90000
  Alex Brown         | VP Marketing     |         1 |              1 | 170000  |               295000
    Sophie Miller    | Marketing Man.   |         2 |              0 | 125000  |               125000

Advanced Recursive Patterns

Cycle Detection: In real-world hierarchical data, you might encounter cycles. Here's how to detect and handle them:

WITH RECURSIVE hierarchy_with_cycle_detection AS (
    SELECT 
        employee_id,
        name,
        manager_id,
        0 as level,
        ARRAY[employee_id] as path_array,
        false as is_cycle
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id,
        e.name,
        e.manager_id,
        h.level + 1,
        h.path_array || e.employee_id,
        e.employee_id = ANY(h.path_array) as is_cycle
    FROM employees e
    JOIN hierarchy_with_cycle_detection h ON e.manager_id = h.employee_id
    WHERE NOT (e.employee_id = ANY(h.path_array))  -- Prevent infinite recursion
       AND h.level < 10  -- Additional safety limit
)

SELECT 
    name,
    level,
    is_cycle,
    CASE 
        WHEN is_cycle THEN 'CYCLE DETECTED'
        ELSE array_to_string(path_array, ' > ')
    END as path_info
FROM hierarchy_with_cycle_detection
ORDER BY level, name;

Graph Traversal with Weighted Edges: For more complex network analysis, you can traverse graphs with weighted relationships:

-- Network routing example
WITH RECURSIVE shortest_path AS (
    SELECT 
        node_id,
        destination_id,
        cost,
        0 as total_cost,
        ARRAY[node_id] as path
    FROM network_links 
    WHERE node_id = 'A'  -- Starting point
    
    UNION ALL
    
    SELECT 
        nl.node_id,
        nl.destination_id,
        nl.cost,
        sp.total_cost + nl.cost,
        sp.path || nl.destination_id
    FROM network_links nl
    JOIN shortest_path sp ON nl.node_id = sp.destination_id
    WHERE NOT (nl.destination_id = ANY(sp.path))  -- Prevent cycles
      AND sp.total_cost + nl.cost < 1000  -- Reasonable cost limit
)

SELECT DISTINCT ON (destination_id)
    destination_id,
    total_cost,
    array_to_string(path || destination_id, ' -> ') as shortest_path
FROM shortest_path
WHERE destination_id IS NOT NULL
ORDER BY destination_id, total_cost;

Performance Optimization and Query Planning

Understanding how the database executes subqueries and CTEs is crucial for writing performant analytical queries. Let's analyze execution patterns and optimization strategies.

Subquery Execution Strategies

Different subquery patterns have vastly different performance characteristics:

-- Correlated subquery: executes once per outer row (SLOW)
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    customer_id,
    order_date,
    order_total,
    (SELECT AVG(order_total) 
     FROM orders o2 
     WHERE o2.customer_id = o1.customer_id 
       AND o2.order_date < o1.order_date) as running_avg
FROM orders o1
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

Typical execution plan:

Sort (cost=125000..130000 rows=20000)
  ->  Nested Loop (cost=0.43..98000 rows=20000)
    ->  Seq Scan on orders o1 (cost=0.00..2500 rows=20000)
    ->  Aggregate (cost=0.43..0.45 rows=1) (loops=20000)
      ->  Index Scan on orders o2 (cost=0.43..0.44 rows=5) (loops=20000)

Notice "loops=20000"—the subquery executes 20,000 times! Here's the optimized version:

-- Window function alternative: single pass (FAST)
EXPLAIN (ANALYZE, BUFFERS)
SELECT 
    customer_id,
    order_date,
    order_total,
    AVG(order_total) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) as running_avg
FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY customer_id, order_date;

Optimized execution plan:

Sort (cost=45000..50000 rows=20000)
  ->  WindowAgg (cost=15000..25000 rows=20000)
    ->  Sort (cost=12000..15000 rows=20000)
      ->  Seq Scan on orders (cost=0.00..2500 rows=20000)

Much better! Single pass through the data with no nested loops.

CTE Materialization vs. Inlining

PostgreSQL and other databases make decisions about whether to materialize CTEs or inline them. Understanding this helps you write more efficient queries:

-- Simple CTE: likely to be inlined
EXPLAIN (ANALYZE, BUFFERS)
WITH recent_orders AS (
    SELECT customer_id, order_total
    FROM orders 
    WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT customer_id, SUM(order_total)
FROM recent_orders
GROUP BY customer_id;

-- Complex CTE: likely to be materialized
EXPLAIN (ANALYZE, BUFFERS)
WITH complex_customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        AVG(order_total) as avg_order_value,
        STDDEV(order_total) as order_value_stddev,
        MIN(order_date) as first_order,
        MAX(order_date) as last_order,
        -- Complex window functions
        AVG(order_total) OVER (
            PARTITION BY EXTRACT(YEAR FROM order_date)
            ORDER BY order_date
            ROWS BETWEEN 10 PRECEDING AND CURRENT ROW
        ) as rolling_seasonal_avg
    FROM orders
    WHERE order_date >= '2022-01-01'
    GROUP BY customer_id, order_date, order_total
)
SELECT * FROM complex_customer_metrics 
WHERE order_count > 5;

When you see "CTE Scan" in the execution plan, the CTE was materialized. When you see the underlying table scans directly, it was inlined.

Index Strategy for Subqueries and CTEs

Proper indexing is crucial for subquery performance. Let's analyze index requirements for common patterns:

-- This query needs specific indexes for optimal performance
WITH customer_segments AS (
    SELECT 
        customer_id,
        NTILE(5) OVER (ORDER BY SUM(order_total)) as value_quintile,
        COUNT(*) as order_frequency
    FROM orders 
    WHERE order_date >= '2023-01-01'
    GROUP BY customer_id
)
SELECT 
    cs.customer_id,
    cs.value_quintile,
    cs.order_frequency,
    (SELECT COUNT(*) 
     FROM orders o 
     WHERE o.customer_id = cs.customer_id 
       AND o.order_date >= '2023-01-01'
       AND o.product_category = 'Electronics') as electronics_orders
FROM customer_segments cs
WHERE cs.value_quintile = 5;  -- Top quintile only

Required indexes for optimal performance:

-- For the main CTE aggregation
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- For the correlated subquery
CREATE INDEX idx_orders_customer_date_category 
ON orders(customer_id, order_date, product_category);

-- For quick customer lookups
CREATE INDEX idx_orders_customer_total 
ON orders(customer_id) INCLUDE (order_total);

Memory and Resource Management

Complex CTEs and recursive queries can consume significant memory. Monitor and control resource usage:

-- Set work memory for complex queries
SET work_mem = '256MB';

-- Monitor query memory usage
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH RECURSIVE large_hierarchy AS (
    -- Your recursive query here
    SELECT ...
);

-- Reset to default
RESET work_mem;

Use these PostgreSQL-specific settings for memory-intensive analytical queries:

• work_mem: Memory for sorting and hash operations
• shared_buffers: Shared memory for caching
• effective_cache_size: Estimated cache size for query planning

Hands-On Exercise

Let's put everything together with a comprehensive analytics challenge. You'll build a customer segmentation and behavior analysis system using multiple CTEs and subqueries.

Scenario: You're analyzing an e-commerce platform with these tables:

• customers (customer_id, registration_date, country, customer_tier)
• orders (order_id, customer_id, order_date, order_total, status)
• order_items (order_item_id, order_id, product_id, quantity, unit_price)
• products (product_id, product_name, category, brand)

Challenge: Create a comprehensive customer analysis that includes:

1. Customer lifetime value and recency metrics
2. Product affinity analysis (most purchased categories by customer segment)
3. Cohort analysis by registration month
4. Identification of at-risk customers (high value but declining activity)

Here's the solution framework—try implementing each CTE before looking at the answers:

-- Customer Segmentation and Behavior Analysis
WITH customer_base_metrics AS (
    -- Your code here: Calculate basic customer metrics
    -- Hint: lifetime value, order count, first/last order dates, avg days between orders
    SELECT 
        c.customer_id,
        c.registration_date,
        c.country,
        c.customer_tier,
        COALESCE(SUM(o.order_total), 0) as lifetime_value,
        COUNT(o.order_id) as total_orders,
        MIN(o.order_date) as first_order_date,
        MAX(o.order_date) as last_order_date,
        CURRENT_DATE - MAX(o.order_date) as days_since_last_order,
        CASE 
            WHEN COUNT(o.order_id) > 1 THEN 
                (MAX(o.order_date) - MIN(o.order_date)) / NULLIF(COUNT(o.order_id) - 1, 0)
            ELSE NULL 
        END as avg_days_between_orders
    FROM customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id
    WHERE o.status = 'completed' OR o.status IS NULL
    GROUP BY c.customer_id, c.registration_date, c.country, c.customer_tier
),

customer_segments AS (
    -- Your code here: Create RFM segments
    -- Hint: Use NTILE for recency, frequency, and monetary value
    SELECT 
        *,
        NTILE(5) OVER (ORDER BY days_since_last_order DESC) as recency_score,
        NTILE(5) OVER (ORDER BY total_orders) as frequency_score,
        NTILE(5) OVER (ORDER BY lifetime_value) as monetary_score,
        CASE 
            WHEN total_orders = 0 THEN 'Never Purchased'
            WHEN days_since_last_order > 365 THEN 'Lost'
            WHEN days_since_last_order > 180 THEN 'At Risk'
            WHEN days_since_last_order > 90 THEN 'Needs Attention'
            WHEN total_orders >= 10 AND lifetime_value > 1000 THEN 'Champion'
            WHEN total_orders >= 5 OR lifetime_value > 500 THEN 'Loyal Customer'
            ELSE 'New Customer'
        END as customer_segment
    FROM customer_base_metrics
),

product_affinity AS (
    -- Your code here: Calculate category preferences by segment
    -- Hint: Use window functions to rank categories within each segment
    SELECT 
        cs.customer_segment,
        p.category,
        COUNT(*) as purchase_count,
        SUM(oi.quantity * oi.unit_price) as category_revenue,
        ROW_NUMBER() OVER (
            PARTITION BY cs.customer_segment 
            ORDER BY COUNT(*) DESC
        ) as category_rank
    FROM customer_segments cs
    JOIN orders o ON cs.customer_id = o.customer_id
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE o.status = 'completed'
    GROUP BY cs.customer_segment, p.category
),

cohort_analysis AS (
    -- Your code here: Registration cohort analysis
    -- Hint: Calculate retention rates by registration month
    SELECT 
        DATE_TRUNC('month', cs.registration_date) as registration_cohort,
        cs.customer_segment,
        COUNT(*) as cohort_size,
        AVG(cs.lifetime_value) as avg_ltv,
        AVG(cs.total_orders) as avg_order_count,
        COUNT(CASE WHEN cs.days_since_last_order <= 90 THEN 1 END) as active_90_days,
        ROUND(
            COUNT(CASE WHEN cs.days_since_last_order <= 90 THEN 1 END) * 100.0 / COUNT(*),
            2
        ) as retention_rate_90d
    FROM customer_segments cs
    WHERE cs.registration_date >= CURRENT_DATE - INTERVAL '2 years'
    GROUP BY DATE_TRUNC('month', cs.registration_date), cs.customer_segment
)

-- Final comprehensive report
SELECT 
    'Customer Segments' as analysis_type,
    cs.customer_segment,
    COUNT(*) as customer_count,
    AVG(cs.lifetime_value) as avg_lifetime_value,
    AVG(cs.total_orders) as avg_total_orders,
    NULL as cohort_month,
    NULL as category,
    NULL as category_rank
FROM customer_segments cs
GROUP BY cs.customer_segment

UNION ALL

SELECT 
    'Top Categories by Segment' as analysis_type,
    pa.customer_segment,
    pa.purchase_count as customer_count,
    pa.category_revenue as avg_lifetime_value,
    NULL as avg_total_orders,
    NULL as cohort_month,
    pa.category,
    pa.category_rank
FROM product_affinity pa
WHERE pa.category_rank <= 3

UNION ALL

SELECT 
    'Cohort Retention' as analysis_type,
    ca.customer_segment,
    ca.cohort_size as customer_count,
    ca.avg_ltv as avg_lifetime_value,
    ca.avg_order_count as avg_total_orders,
    ca.registration_cohort as cohort_month,
    NULL as category,
    NULL as category_rank
FROM cohort_analysis ca

ORDER BY analysis_type, customer_segment, category_rank;

Extension Challenge: Add a recursive CTE to analyze referral networks if you have a customer referral table, or build a product recommendation engine based on frequently bought-together patterns.

Common Mistakes & Troubleshooting

Performance Anti-Patterns

Mistake 1: Correlated Subqueries in Large Tables

-- DON'T: This will scan the orders table once per product
SELECT 
    product_id,
    product_name,
    (SELECT COUNT(*) 
     FROM orders o 
     JOIN order_items oi ON o.order_id = oi.order_id 
     WHERE oi.product_id = p.product_id) as total_sold
FROM products p;

-- DO: Use a proper JOIN or window function
WITH product_sales AS (
    SELECT 
        oi.product_id,
        COUNT(*) as total_sold
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY oi.product_id
)
SELECT 
    p.product_id,
    p.product_name,
    COALESCE(ps.total_sold, 0) as total_sold
FROM products p
LEFT JOIN product_sales ps ON p.product_id = ps.product_id;

Mistake 2: Unnecessary CTE Materialization

-- DON'T: Complex CTE used only once
WITH complex_calculation AS (
    SELECT 
        customer_id,
        SUM(order_total) * 0.1 as loyalty_points  -- Simple calculation
    FROM orders
    GROUP BY customer_id
)
SELECT customer_id, loyalty_points
FROM complex_calculation
WHERE loyalty_points > 100;

-- DO: Inline simple calculations
SELECT 
    customer_id,
    SUM(order_total) * 0.1 as loyalty_points
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) * 0.1 > 100;

Mistake 3: Recursive CTE Without Proper Termination

-- DON'T: Risk of infinite recursion
WITH RECURSIVE hierarchy AS (
    SELECT employee_id, manager_id, 0 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.employee_id, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
    -- Missing cycle detection and depth limits!
)
SELECT * FROM hierarchy;

-- DO: Add proper safeguards
WITH RECURSIVE hierarchy AS (
    SELECT 
        employee_id, 
        manager_id, 
        0 as level,
        ARRAY[employee_id] as path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT 
        e.employee_id, 
        e.manager_id, 
        h.level + 1,
        h.path || e.employee_id
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
    WHERE h.level < 10  -- Depth limit
      AND NOT (e.employee_id = ANY(h.path))  -- Cycle detection
)
SELECT * FROM hierarchy;

Debugging Complex Queries

When your complex CTE query isn't returning expected results:

1. Test CTEs individually:

-- Test each CTE separately
WITH first_cte AS (
    SELECT customer_id, SUM(order_total) as total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM first_cte LIMIT 10;  -- Verify results

2. Add intermediate columns for debugging:

WITH customer_metrics AS (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(order_total) as lifetime_value,
        -- Debug columns
        MIN(order_date) as debug_first_order,
        MAX(order_date) as debug_last_order,
        COUNT(DISTINCT DATE_TRUNC('month', order_date)) as debug_active_months
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_metrics WHERE customer_id = 12345;

3. Check for NULL handling:

-- Common NULL issues in CTEs
WITH customer_stats AS (
    SELECT 
        customer_id,
        AVG(order_total) as avg_order_value,
        -- This might be NULL for single-order customers
        STDDEV(order_total) as order_stddev
    FROM orders
    GROUP BY customer_id
)
SELECT 
    customer_id,
    avg_order_value,
    COALESCE(order_stddev, 0) as order_stddev_safe  -- Handle NULLs
FROM customer_stats;

Memory and Resource Issues

Monitor memory usage for large CTEs:

-- PostgreSQL: Check query memory usage
SELECT 
    query,
    total_time,
    calls,
    mean_time,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
WHERE query ILIKE '%WITH%'
ORDER BY temp_blks_written DESC;

Optimize memory-intensive operations:

-- Use LIMIT in CTEs when appropriate
WITH top_customers AS (
    SELECT customer_id, SUM(order_total) as total_spend
    FROM orders
    GROUP BY customer_id
    ORDER BY SUM(order_total) DESC
    LIMIT 1000  -- Don't process all customers if you only need top ones
)
SELECT * FROM top_customers;

Advanced Integration Patterns

CTEs with Window Functions

Complex analytical queries often combine CTEs with advanced window functions:

WITH daily_metrics AS (
    SELECT 
        DATE_TRUNC('day', order_date) as order_date,
        COUNT(*) as daily_orders,
        SUM(order_total) as daily_revenue,
        COUNT(DISTINCT customer_id) as daily_unique_customers
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY DATE_TRUNC('day', order_date)
),

trend_analysis AS (
    SELECT 
        order_date,
        daily_orders,
        daily_revenue,
        daily_unique_customers,
        -- Moving averages
        AVG(daily_revenue) OVER (
            ORDER BY order_date 
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ) as seven_day_avg_revenue,
        -- Percent change calculations
        LAG(daily_revenue, 7) OVER (ORDER BY order_date) as revenue_7_days_ago,
        -- Ranking within periods
        RANK() OVER (
            PARTITION BY DATE_TRUNC('week', order_date)
            ORDER BY daily_revenue DESC
        ) as daily_revenue_rank_in_week,
        -- Running totals
        SUM(daily_revenue) OVER (
            ORDER BY order_date
        ) as running_total_revenue
    FROM daily_metrics
)

SELECT 
    order_date,
    daily_revenue,
    seven_day_avg_revenue,
    CASE 
        WHEN revenue_7_days_ago > 0 THEN 
            ROUND(((daily_revenue - revenue_7_days_ago) / revenue_7_days_ago * 100), 2)
        ELSE NULL
    END as week_over_week_pct_change,
    daily_revenue_rank_in_week,
    running_total_revenue
FROM trend_analysis
ORDER BY order_date;

CTEs in Data Pipeline Architecture

In production analytics environments, CTEs enable sophisticated data transformation pipelines:

-- Multi-stage data transformation pipeline
WITH raw_events AS (
    -- Stage 1: Raw data extraction with quality filters
    SELECT 
        event_id,
        user_id,
        event_type,
        event_timestamp,
        event_properties,
        device_type,
        session_id
    FROM user_events
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '7 days'
      AND user_id IS NOT NULL
      AND event_type IN ('page_view', 'purchase', 'add_to_cart', 'search')
),

cleaned_events AS (
    -- Stage 2: Data cleaning and standardization
    SELECT 
        event_id,
        user_id,
        event_type,
        event_timestamp,
        -- Clean and parse JSON properties
        CASE 
            WHEN event_properties::json->>'page_url' IS NOT NULL 
            THEN event_properties::json->>'page_url'
            ELSE NULL
        END as page_url,
        CASE 
            WHEN event_properties::json->>'purchase_amount' ~ '^[0-9]+\.?[0-9]*$'
            THEN (event_properties::json->>'purchase_amount')::numeric
            ELSE NULL
        END as purchase_amount,
        -- Standardize device categories
        CASE 
            WHEN device_type ILIKE '%mobile%' OR device_type ILIKE '%phone%' THEN 'Mobile'
            WHEN device_type ILIKE '%tablet%' THEN 'Tablet'
            ELSE 'Desktop'
        END as device_category,
        session_id
    FROM raw_events
    WHERE event_timestamp > CURRENT_TIMESTAMP - INTERVAL '7 days'
),

session_metrics AS (
    -- Stage 3: Session-level aggregations
    SELECT 
        session_id,
        user_id,
        device_category,
        MIN(event_timestamp) as session_start,
        MAX(event_timestamp) as session_end,
        COUNT(*) as total_events,
        COUNT(CASE WHEN event_type = 'page_view' THEN 1 END) as page_views,
        COUNT(CASE WHEN event_type = 'purchase' THEN 1 END) as purchases,
        SUM(COALESCE(purchase_amount, 0)) as session_revenue,
        -- Session duration in minutes
        EXTRACT(EPOCH FROM (MAX(event_timestamp) - MIN(event_timestamp))) / 60 as session_duration_minutes
    FROM cleaned_events
    GROUP BY session_id, user_id, device_category
    HAVING COUNT(*) >= 3  -- Filter out very short sessions
),

user_behavior_summary AS (
    -- Stage 4: User-level behavioral analysis
    SELECT 
        user_id,
        COUNT(DISTINCT session_id) as total_sessions,
        SUM(session_revenue) as total_revenue,
        AVG(session_duration_minutes) as avg_session_duration,
        SUM(page_views) as total_page_views,
        SUM(purchases) as total_purchases,
        -- Behavioral segmentation
        CASE 
            WHEN SUM(purchases) >= 5 THEN 'High Frequency Buyer'
            WHEN SUM(purchases) >= 2 THEN 'Repeat Buyer'
            WHEN SUM(purchases) = 1 THEN 'Single Purchase'
            WHEN SUM(page_views) >= 20 THEN 'Browser'
            ELSE 'Low Engagement'
        END as user_segment,
        -- Device preference
        MODE() WITHIN GROUP (ORDER BY device_category) as preferred_device
    FROM session_metrics
    GROUP BY user_id
)

-- Final output: actionable business intelligence
SELECT 
    user_segment,
    preferred_device,
    COUNT(*) as user_count,
    AVG(total_revenue) as avg_revenue_per_user,
    AVG(total_sessions) as avg_sessions_per_user,
    AVG(avg_session_duration) as avg_session_duration,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_revenue) as median_revenue,
    -- Conversion rates
    AVG(total_purchases::numeric / NULLIF(total_sessions, 0)) as avg_purchase_conversion_rate
FROM user_behavior_summary
GROUP BY user_segment, preferred_device
ORDER BY avg_revenue_per_user DESC;

This pipeline demonstrates how CTEs enable complex multi-stage data transformations while maintaining readability and debuggability.

Summary & Next Steps

You've now mastered the sophisticated art of subqueries and CTEs—tools that separate competent SQL users from true database professionals. The patterns you've learned here form the foundation of advanced analytics, data engineering pipelines, and complex business intelligence systems.

Key concepts you've mastered:

• Subquery optimization: Understanding when correlated subqueries hurt performance and how to refactor them using window functions or CTEs
• CTE architecture: Building readable, maintainable analytical queries through logical data transformation stages
• Recursive patterns: Navigating hierarchical data structures and complex relationships within SQL
• Performance considerations: Query planning, index strategies, and memory management for complex analytical workloads
• Production patterns: Integration with window functions, data pipeline architecture, and real-world analytical challenges

The true power of these techniques emerges when you combine them thoughtfully. A well-architected CTE pipeline can replace complex application logic, reduce data movement between systems, and provide insights that would be difficult or impossible to achieve with simple queries.

Immediate next steps:

1. Practice with your own data: Take a complex analytical question from your work and break it down into CTEs. Focus on making each step logical and testable.

2. Study query execution plans: Use EXPLAIN ANALYZE on your complex queries to understand how the database processes your CTEs and subqueries. Look for opportunities to optimize.

3. Build reusable patterns: Create a library of CTE patterns for common analytical tasks—cohort analysis, running calculations, hierarchical rollups, and trend analysis.

Advanced topics to explore:

• Materialized CTEs and views: When and how to persist intermediate results for better performance
• Cross-database analytics: Using CTEs with federated queries and external data sources
• Time-series analysis: Advanced window functions combined with CTEs for complex temporal analytics
• Graph analytics: Using recursive CTEs for network analysis, shortest path algorithms, and relationship mining

The mastery you've developed here opens doors to more advanced database techniques: query optimization at scale, analytical database design, and even contributing to database engine development. Your next challenge is to apply these patterns to increasingly complex real-world problems, always balancing sophistication with maintainability.

Remember: the best query is not the most clever one—it's the one that solves the business problem efficiently while remaining understandable to future maintainers. The CTEs and subqueries you write today should make tomorrow's analysis easier, not harder.