Combining SQL Concepts: Nested Queries and Complex Expressions

Now that you've mastered individual SQL concepts—SELECT, WHERE, JOINs, subqueries, CASE, UNION, aggregate functions—it's time to combine them into powerful, real-world queries. Most production queries don't use just one technique; they layer multiple concepts together: a subquery in the WHERE clause with JOINs, nested CASE expressions with aggregate functions, UNION queries with complex filtering. This lesson shows you how to read, write, and debug complex SQL by combining everything you've learned. You'll see nested functions (string operations wrapped in date functions wrapped in CASE statements), correlated subqueries with multiple JOINs, and queries that synthesize 5+ concepts simultaneously. Think of this as your "capstone" lesson—where all the pieces come together to solve sophisticated data problems.

📚 What You'll Learn

• How to nest subqueries in SELECT, FROM, and WHERE clauses
• How to nest functions (string, date, math) within each other
• How to combine JOINs with subqueries
• How to use nested CASE expressions
• How to layer aggregate functions with filtering
• How to combine UNION with complex queries
• How to read and understand deeply nested SQL
• Performance considerations for complex queries
• Debugging strategies for complex expressions
• Real-world patterns combining 5+ concepts

1. Understanding Query Structure

Anatomy of a Complex Query

-- Complex query example
SELECT 
    c.customer_id,
    c.name,
    -- Nested function in SELECT
    CONCAT(
        UPPER(LEFT(c.name, 1)),
        LOWER(SUBSTRING(c.name, 2))
    ) AS formatted_name,
    
    -- Subquery in SELECT
    (SELECT COUNT(*) 
     FROM orders o 
     WHERE o.customer_id = c.customer_id) AS order_count,
    
    -- Nested CASE with subquery
    CASE
        WHEN (SELECT SUM(total) 
              FROM orders o 
              WHERE o.customer_id = c.customer_id) > 1000 THEN 'VIP'
        WHEN (SELECT COUNT(*) 
              FROM orders o 
              WHERE o.customer_id = c.customer_id) > 5 THEN 'Regular'
        ELSE 'New'
    END AS customer_tier

FROM customers c
-- Subquery in JOIN
INNER JOIN (
    SELECT customer_id, MAX(order_date) AS last_order
    FROM orders
    GROUP BY customer_id
) AS recent ON c.customer_id = recent.customer_id

-- Subquery in WHERE
WHERE c.customer_id IN (
    SELECT DISTINCT customer_id 
    FROM orders 
    WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
)

ORDER BY order_count DESC
LIMIT 10;

2. Nested Subqueries in Different Clauses

Subqueries in SELECT (Scalar Subqueries)

-- Each subquery returns a single value per row
SELECT 
    p.product_id,
    p.name,
    p.price,
    
    -- Average price in category
    (SELECT AVG(price) 
     FROM products 
     WHERE category = p.category) AS category_avg_price,
    
    -- Price compared to category average
    ROUND(p.price - (SELECT AVG(price) 
                      FROM products 
                      WHERE category = p.category), 2) AS price_vs_avg,
    
    -- Total sales for this product
    (SELECT COUNT(*) 
     FROM order_items oi 
     WHERE oi.product_id = p.product_id) AS units_sold,
    
    -- Revenue for this product
    (SELECT SUM(oi.quantity * oi.price) 
     FROM order_items oi 
     WHERE oi.product_id = p.product_id) AS total_revenue

FROM products p
ORDER BY total_revenue DESC;

Subqueries in FROM (Derived Tables)

-- Subquery creates a temporary table
SELECT 
    category,
    product_count,
    avg_price,
    total_revenue,
    ROUND(total_revenue / product_count, 2) AS revenue_per_product
FROM (
    -- Inner query aggregates by category
    SELECT 
        p.category,
        COUNT(*) AS product_count,
        AVG(p.price) AS avg_price,
        (SELECT SUM(oi.quantity * oi.price)
         FROM order_items oi
         INNER JOIN products p2 ON oi.product_id = p2.product_id
         WHERE p2.category = p.category) AS total_revenue
    FROM products p
    GROUP BY p.category
) AS category_stats
WHERE total_revenue > 10000
ORDER BY total_revenue DESC;

Nested Subqueries in WHERE

-- Multiple levels of nesting
SELECT 
    customer_id,
    name,
    email
FROM customers
WHERE customer_id IN (
    -- Customers who ordered products
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_id IN (
        -- Orders containing electronics
        SELECT order_id
        FROM order_items oi
        INNER JOIN products p ON oi.product_id = p.product_id
        WHERE p.category = 'Electronics'
        AND oi.product_id IN (
            -- High-value products only
            SELECT product_id
            FROM products
            WHERE price > 500
        )
    )
    AND order_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
);

3. Nesting Functions

String Function Nesting

-- Complex string manipulation
SELECT 
    customer_id,
    email,
    
    -- Extract and format domain
    UPPER(
        SUBSTRING_INDEX(
            SUBSTRING_INDEX(email, '@', -1),
            '.',
            1
        )
    ) AS email_provider,
    
    -- Format name as "Last, First"
    CONCAT(
        UPPER(SUBSTRING_INDEX(name, ' ', -1)),
        ', ',
        UPPER(LEFT(SUBSTRING_INDEX(name, ' ', 1), 1)),
        LOWER(SUBSTRING(SUBSTRING_INDEX(name, ' ', 1), 2))
    ) AS formatted_name,
    
    -- Clean and format phone
    CONCAT(
        '(',
        SUBSTRING(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', ''), 1, 3),
        ') ',
        SUBSTRING(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', ''), 4, 3),
        '-',
        SUBSTRING(REPLACE(REPLACE(REPLACE(phone, '-', ''), '(', ''), ')', ''), 7, 4)
    ) AS formatted_phone

FROM customers;

Date Function Nesting

-- Complex date calculations
SELECT 
    order_id,
    order_date,
    ship_date,
    
    -- Days until shipping
    DATEDIFF(ship_date, order_date) AS days_to_ship,
    
    -- Formatted with day name
    CONCAT(
        DAYNAME(order_date),
        ', ',
        DATE_FORMAT(order_date, '%M %d, %Y')
    ) AS order_date_formatted,
    
    -- Age of order
    CONCAT(
        FLOOR(DATEDIFF(CURDATE(), order_date) / 365), ' years, ',
        FLOOR((DATEDIFF(CURDATE(), order_date) % 365) / 30), ' months'
    ) AS order_age,
    
    -- Quarter and year
    CONCAT('Q', QUARTER(order_date), ' ', YEAR(order_date)) AS order_quarter

FROM orders;

Math Function Nesting

-- Complex calculations
SELECT 
    product_id,
    name,
    price,
    cost,
    
    -- Profit margin percentage
    ROUND(
        (price - cost) / NULLIF(price, 0) * 100,
        2
    ) AS margin_pct,
    
    -- Price with tax and discount
    ROUND(
        price * (1 + 0.0825) * (1 - IFNULL(discount_pct, 0) / 100),
        2
    ) AS final_price,
    
    -- Format as currency
    CONCAT(
        '$',
        FORMAT(
            ROUND(
                price * (1 + 0.0825) * (1 - IFNULL(discount_pct, 0) / 100),
                2
            ),
            2
        )
    ) AS price_display

FROM products;

4. Nested CASE Expressions

Multi-Level Decision Logic

-- Complex categorization
SELECT 
    customer_id,
    name,
    total_orders,
    total_spent,
    last_order_date,
    
    -- Multi-factor customer tier
    CASE
        WHEN total_spent > 10000 THEN
            CASE
                WHEN DATEDIFF(CURDATE(), last_order_date) <= 30 THEN 'VIP Active'
                WHEN DATEDIFF(CURDATE(), last_order_date) <= 90 THEN 'VIP'
                ELSE 'VIP Inactive'
            END
        WHEN total_spent > 5000 THEN
            CASE
                WHEN total_orders > 20 THEN 'Gold Frequent'
                WHEN DATEDIFF(CURDATE(), last_order_date) <= 60 THEN 'Gold Active'
                ELSE 'Gold'
            END
        WHEN total_spent > 1000 THEN
            CASE
                WHEN total_orders > 10 THEN 'Silver Frequent'
                ELSE 'Silver'
            END
        ELSE
            CASE
                WHEN DATEDIFF(CURDATE(), last_order_date) <= 30 THEN 'Bronze Active'
                ELSE 'Bronze'
            END
    END AS customer_tier

FROM customer_summary;

5. Combining JOINs and Subqueries

Subquery-Derived Tables in JOINs

-- Join with aggregated subquery results
SELECT 
    c.customer_id,
    c.name,
    stats.order_count,
    stats.total_spent,
    stats.avg_order_value,
    recent.last_order_date,
    recent.days_since_order,
    
    -- Tier based on combined metrics
    CASE
        WHEN stats.total_spent > 5000 AND recent.days_since_order < 30 THEN 'VIP Active'
        WHEN stats.total_spent > 5000 THEN 'VIP'
        WHEN stats.order_count > 10 THEN 'Frequent'
        ELSE 'Regular'
    END AS tier

FROM customers c

-- Join with order statistics
INNER JOIN (
    SELECT 
        customer_id,
        COUNT(*) AS order_count,
        SUM(total) AS total_spent,
        AVG(total) AS avg_order_value
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) AS stats ON c.customer_id = stats.customer_id

-- Join with recency data
INNER JOIN (
    SELECT 
        customer_id,
        MAX(order_date) AS last_order_date,
        DATEDIFF(CURDATE(), MAX(order_date)) AS days_since_order
    FROM orders
    GROUP BY customer_id
) AS recent ON c.customer_id = recent.customer_id

WHERE stats.order_count > 0
ORDER BY stats.total_spent DESC;

6. UNION with Nested Queries

Combining Multiple Complex Queries

-- Unified customer activity report
SELECT 
    'New Customer' AS event_type,
    customer_id,
    name AS description,
    registration_date AS event_date,
    0 AS amount
FROM customers
WHERE registration_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

UNION ALL

SELECT 
    'Order' AS event_type,
    o.customer_id,
    CONCAT('Order #', o.order_id, ' - ', 
           (SELECT COUNT(*) FROM order_items WHERE order_id = o.order_id),
           ' items') AS description,
    o.order_date AS event_date,
    o.total AS amount
FROM orders o
WHERE o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

UNION ALL

SELECT 
    'Review' AS event_type,
    r.customer_id,
    CONCAT('Reviewed ', p.name, ' - ', r.rating, ' stars') AS description,
    r.review_date AS event_date,
    0 AS amount
FROM reviews r
INNER JOIN products p ON r.product_id = p.product_id
WHERE r.review_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

ORDER BY event_date DESC, customer_id;

7. Complex Aggregation Patterns

Conditional Aggregation

-- Product performance with multiple metrics
SELECT 
    p.product_id,
    p.name,
    p.category,
    
    -- Total metrics
    COUNT(DISTINCT o.order_id) AS total_orders,
    SUM(oi.quantity) AS total_units_sold,
    SUM(oi.quantity * oi.price) AS total_revenue,
    
    -- Conditional counts
    SUM(CASE WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
             THEN oi.quantity ELSE 0 END) AS units_last_30_days,
    
    SUM(CASE WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
             THEN oi.quantity ELSE 0 END) AS units_last_90_days,
    
    -- Average with filtering
    ROUND(AVG(CASE WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
                   THEN oi.price END), 2) AS avg_recent_price,
    
    -- Percentage calculations
    ROUND(
        SUM(CASE WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
                 THEN oi.quantity ELSE 0 END) /
        NULLIF(SUM(oi.quantity), 0) * 100,
        2
    ) AS pct_sales_last_30_days

FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.status = 'completed'
GROUP BY p.product_id, p.name, p.category
HAVING total_units_sold > 0
ORDER BY total_revenue DESC;

8. Performance Tips for Complex Queries

Optimizing Subqueries

-- ❌ SLOW: Correlated subquery in SELECT
SELECT 
    c.customer_id,
    c.name,
    (SELECT SUM(total) FROM orders WHERE customer_id = c.customer_id) AS total_spent
FROM customers c;

-- ✅ FASTER: JOIN with aggregated subquery
SELECT 
    c.customer_id,
    c.name,
    COALESCE(o.total_spent, 0) AS total_spent
FROM customers c
LEFT JOIN (
    SELECT customer_id, SUM(total) AS total_spent
    FROM orders
    GROUP BY customer_id
) o ON c.customer_id = o.customer_id;

9. Debugging Strategies

10. 🎯 Practice Exercises

customers (customer_id, name, email, registration_date)
orders (order_id, customer_id, order_date, total, status)
order_items (item_id, order_id, product_id, quantity, price)
products (product_id, name, category, price)

SELECT 
    c.customer_id,
    c.name,
    c.email,
    
    -- Basic metrics with subqueries
    (SELECT COUNT(*) 
     FROM orders o 
     WHERE o.customer_id = c.customer_id 
       AND o.status = 'completed') AS order_count,
    
    COALESCE(
        (SELECT SUM(o.total) 
         FROM orders o 
         WHERE o.customer_id = c.customer_id 
           AND o.status = 'completed'),
        0
    ) AS lifetime_value,
    
    -- Average order value with nested calculation
    ROUND(
        COALESCE(
            (SELECT SUM(o.total) 
             FROM orders o 
             WHERE o.customer_id = c.customer_id 
               AND o.status = 'completed'),
            0
        ) / NULLIF(
            (SELECT COUNT(*) 
             FROM orders o 
             WHERE o.customer_id = c.customer_id 
               AND o.status = 'completed'),
            0
        ),
        2
    ) AS avg_order_value,
    
    -- Days since last order
    COALESCE(
        DATEDIFF(
            CURDATE(),
            (SELECT MAX(order_date) 
             FROM orders 
             WHERE customer_id = c.customer_id)
        ),
        DATEDIFF(CURDATE(), c.registration_date)
    ) AS days_since_last_order,
    
    -- Most purchased category (subquery)
    (SELECT p.category
     FROM order_items oi
     INNER JOIN orders o ON oi.order_id = o.order_id
     INNER JOIN products p ON oi.product_id = p.product_id
     WHERE o.customer_id = c.customer_id
       AND o.status = 'completed'
     GROUP BY p.category
     ORDER BY SUM(oi.quantity) DESC
     LIMIT 1) AS favorite_category,
    
    -- Multi-level customer tier (nested CASE)
    CASE
        WHEN COALESCE(
            (SELECT SUM(o.total) 
             FROM orders o 
             WHERE o.customer_id = c.customer_id 
               AND o.status = 'completed'),
            0
        ) > 10000 THEN
            CASE
                WHEN DATEDIFF(CURDATE(), (SELECT MAX(order_date) 
                                          FROM orders 
                                          WHERE customer_id = c.customer_id)) <= 30 
                    THEN 'VIP Active'
                WHEN DATEDIFF(CURDATE(), (SELECT MAX(order_date) 
                                          FROM orders 
                                          WHERE customer_id = c.customer_id)) <= 90 
                    THEN 'VIP Recent'
                ELSE 'VIP Inactive'
            END
        WHEN COALESCE(
            (SELECT SUM(o.total) 
             FROM orders o 
             WHERE o.customer_id = c.customer_id 
               AND o.status = 'completed'),
            0
        ) > 5000 THEN 'Gold'
        WHEN COALESCE(
            (SELECT COUNT(*) 
             FROM orders o 
             WHERE o.customer_id = c.customer_id 
               AND o.status = 'completed'),
            0
        ) > 5 THEN 'Silver'
        ELSE 'Bronze'
    END AS customer_tier

FROM customers c
ORDER BY lifetime_value DESC;

products (product_id, name, category, price, cost, stock)
orders (order_id, order_date, status)
order_items (item_id, order_id, product_id, quantity, price)

SELECT 
    p.product_id,
    p.name,
    p.category,
    CONCAT('$', FORMAT(p.price, 2)) AS price_display,
    
    -- Sales metrics
    COALESCE(SUM(oi.quantity), 0) AS total_units_sold,
    CONCAT('$', FORMAT(COALESCE(SUM(oi.quantity * oi.price), 0), 2)) AS total_revenue,
    CONCAT('$', FORMAT(
        COALESCE(SUM(oi.quantity * (oi.price - p.cost)), 0),
        2
    )) AS total_profit,
    
    -- Profit margin
    ROUND(
        COALESCE(
            SUM(oi.quantity * (oi.price - p.cost)) /
            NULLIF(SUM(oi.quantity * oi.price), 0) * 100,
            0
        ),
        2
    ) AS profit_margin_pct,
    
    -- Compare to category average (correlated subquery)
    ROUND(
        p.price - (SELECT AVG(price) 
                   FROM products 
                   WHERE category = p.category),
        2
    ) AS price_vs_category_avg,
    
    -- Time-based trends (conditional aggregation)
    SUM(CASE 
        WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
        THEN oi.quantity 
        ELSE 0 
    END) AS units_last_30_days,
    
    SUM(CASE 
        WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 60 DAY) 
        THEN oi.quantity 
        ELSE 0 
    END) AS units_last_60_days,
    
    SUM(CASE 
        WHEN o.order_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY) 
        THEN oi.quantity 
        ELSE 0 
    END) AS units_last_90_days,
    
    -- Stock status with nested logic
    CASE
        WHEN p.stock = 0 THEN '❌ Out of Stock'
        WHEN p.stock < (
            SELECT AVG(quantity)
            FROM order_items oi2
            INNER JOIN orders o2 ON oi2.order_id = o2.order_id
            WHERE oi2.product_id = p.product_id
              AND o2.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        ) * 7 THEN '⚠️ Low Stock'
        ELSE '✅ In Stock'
    END AS stock_status,
    
    -- Performance score (nested CASE with multiple factors)
    CASE
        WHEN COALESCE(SUM(oi.quantity), 0) > (
            SELECT AVG(total_sold)
            FROM (
                SELECT SUM(quantity) AS total_sold
                FROM order_items oi3
                INNER JOIN orders o3 ON oi3.order_id = o3.order_id
                WHERE o3.status = 'completed'
                GROUP BY oi3.product_id
            ) AS product_sales
        ) THEN
            CASE
                WHEN COALESCE(
                    SUM(oi.quantity * (oi.price - p.cost)) /
                    NULLIF(SUM(oi.quantity * oi.price), 0) * 100,
                    0
                ) > 40 THEN '⭐⭐⭐ Excellent'
                WHEN COALESCE(
                    SUM(oi.quantity * (oi.price - p.cost)) /
                    NULLIF(SUM(oi.quantity * oi.price), 0) * 100,
                    0
                ) > 25 THEN '⭐⭐ Very Good'
                ELSE '⭐ Good Sales'
            END
        WHEN COALESCE(SUM(oi.quantity), 0) > 10 THEN 'Average'
        ELSE 'Needs Attention'
    END AS performance_rating

FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
LEFT JOIN orders o ON oi.order_id = o.order_id AND o.status = 'completed'
GROUP BY p.product_id, p.name, p.category, p.price, p.cost, p.stock
ORDER BY total_revenue DESC;

customers (customer_id, name, email, registration_date, country)
orders (order_id, customer_id, order_date, total, status)
order_items (item_id, order_id, product_id, quantity, price)
products (product_id, name, category, brand, price, cost)
reviews (review_id, product_id, customer_id, rating, review_date)

-- Executive Dashboard: Customer Segments Analysis
SELECT 
    segment_name,
    customer_count,
    total_customers,
    CONCAT(ROUND(customer_count / total_customers * 100, 1), '%') AS segment_percentage,
    avg_lifetime_value,
    avg_order_count,
    avg_satisfaction,
    top_category,
    growth_trend
FROM (
    SELECT 
        -- Segment definition with nested CASE
        CASE
            WHEN lifetime_value > 10000 THEN
                CASE
                    WHEN recency_days <= 30 THEN 'VIP Active'
                    WHEN recency_days <= 90 THEN 'VIP Recent'
                    ELSE 'VIP At Risk'
                END
            WHEN lifetime_value > 5000 THEN 'High Value'
            WHEN order_count > 10 THEN 'Frequent Buyer'
            WHEN recency_days <= 90 THEN 'Active'
            ELSE 'Inactive'
        END AS segment_name,
        
        COUNT(*) AS customer_count,
        
        -- Total for percentage calculation
        (SELECT COUNT(*) FROM customers) AS total_customers,
        
        -- Aggregated metrics with nested calculations
        CONCAT('$', FORMAT(ROUND(AVG(lifetime_value), 2), 2)) AS avg_lifetime_value,
        ROUND(AVG(order_count), 1) AS avg_order_count,
        CONCAT(ROUND(AVG(avg_rating), 2), ' ⭐') AS avg_satisfaction,
        
        -- Most common category per segment (nested subquery)
        (SELECT p.category
         FROM order_items oi
         INNER JOIN orders o ON oi.order_id = o.order_id
         INNER JOIN products p ON oi.product_id = p.product_id
         WHERE o.customer_id IN (
             SELECT c2.customer_id
             FROM customers c2
             INNER JOIN (
                 SELECT 
                     o2.customer_id,
                     COUNT(*) AS cnt,
                     SUM(o2.total) AS ltv,
                     DATEDIFF(CURDATE(), MAX(o2.order_date)) AS rec_days
                 FROM orders o2
                 WHERE o2.status = 'completed'
                 GROUP BY o2.customer_id
             ) stats ON c2.customer_id = stats.customer_id
             WHERE 
                 CASE
                     WHEN stats.ltv > 10000 THEN
                         CASE
                             WHEN stats.rec_days <= 30 THEN 'VIP Active'
                             WHEN stats.rec_days <= 90 THEN 'VIP Recent'
                             ELSE 'VIP At Risk'
                         END
                     WHEN stats.ltv > 5000 THEN 'High Value'
                     WHEN stats.cnt > 10 THEN 'Frequent Buyer'
                     WHEN stats.rec_days <= 90 THEN 'Active'
                     ELSE 'Inactive'
                 END = segment_stats.segment_name
         )
         GROUP BY p.category
         ORDER BY SUM(oi.quantity) DESC
         LIMIT 1) AS top_category,
        
        -- Growth trend (comparing recent vs older periods)
        CASE
            WHEN AVG(CASE 
                    WHEN recency_days <= 30 THEN order_count 
                    ELSE 0 
                 END) > 
                 AVG(CASE 
                    WHEN recency_days BETWEEN 31 AND 60 THEN order_count 
                    ELSE 0 
                 END) * 1.2 
            THEN '📈 Growing'
            WHEN AVG(CASE 
                    WHEN recency_days <= 30 THEN order_count 
                    ELSE 0 
                 END) < 
                 AVG(CASE 
                    WHEN recency_days BETWEEN 31 AND 60 THEN order_count 
                    ELSE 0 
                 END) * 0.8 
            THEN '📉 Declining'
            ELSE '➡️ Stable'
        END AS growth_trend
        
    FROM (
        -- Base customer metrics
        SELECT 
            c.customer_id,
            c.name,
            c.country,
            
            -- Order metrics with subqueries
            COALESCE(
                (SELECT COUNT(*) 
                 FROM orders o 
                 WHERE o.customer_id = c.customer_id 
                   AND o.status = 'completed'),
                0
            ) AS order_count,
            
            COALESCE(
                (SELECT SUM(total) 
                 FROM orders o 
                 WHERE o.customer_id = c.customer_id 
                   AND o.status = 'completed'),
                0
            ) AS lifetime_value,
            
            -- Recency
            COALESCE(
                DATEDIFF(CURDATE(), (
                    SELECT MAX(order_date) 
                    FROM orders 
                    WHERE customer_id = c.customer_id
                )),
                999
            ) AS recency_days,
            
            -- Average rating (nested aggregation)
            COALESCE(
                (SELECT AVG(rating) 
                 FROM reviews r 
                 WHERE r.customer_id = c.customer_id),
                0
            ) AS avg_rating
            
        FROM customers c
    ) AS customer_metrics
) AS segment_stats
GROUP BY segment_name, total_customers
ORDER BY 
    CASE segment_name
        WHEN 'VIP Active' THEN 1
        WHEN 'VIP Recent' THEN 2
        WHEN 'VIP At Risk' THEN 3
        WHEN 'High Value' THEN 4
        WHEN 'Frequent Buyer' THEN 5
        WHEN 'Active' THEN 6
        ELSE 7
    END;

📝 Key Takeaways

• Read complex queries inside-out starting with innermost subqueries
• Subqueries in SELECT execute once per row (correlated)
• Subqueries in FROM create derived tables (executed once)
• Subqueries in WHERE filter results efficiently
• Nest functions by combining string, date, and math operations
• Nested CASE enables multi-level decision logic
• JOINs with subqueries combine aggregated data effectively
• UNION can combine multiple complex queries
• Conditional aggregation creates pivot-style reports
• Performance matters - correlated subqueries can be slow
• Test incrementally - build and verify step by step
• Use meaningful aliases to keep track of derived data
• Comment complex logic for maintainability
• EXPLAIN is your friend for optimization
• Practice combining concepts to solve real-world problems