Bits of .NET

The Subquery Nightmare

-- ❌ Nested subqueries: Hard to read
SELECT 
    u.name,
    (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) as order_count,
    (SELECT AVG(total) FROM orders o WHERE o.user_id = u.id) as avg_order
FROM users u
WHERE u.id IN (
    SELECT user_id 
    FROM orders 
    WHERE created_at > '2024-01-01'
    GROUP BY user_id
    HAVING COUNT(*) > 5
)
AND u.country IN (
    SELECT country 
    FROM countries 
    WHERE region = 'EU'
);

-- What does this do? Hard to understand at a glance

CTE Solution: Clear and Readable

-- ✅ WITH clause: Self-documenting
WITH 
    active_users AS (
        SELECT user_id
        FROM orders
        WHERE created_at > '2024-01-01'
        GROUP BY user_id
        HAVING COUNT(*) > 5
    ),
    eu_countries AS (
        SELECT country
        FROM countries
        WHERE region = 'EU'
    ),
    user_stats AS (
        SELECT 
            user_id,
            COUNT(*) as order_count,
            AVG(total) as avg_order
        FROM orders
        GROUP BY user_id
    )
SELECT 
    u.name,
    s.order_count,
    s.avg_order
FROM users u
INNER JOIN active_users a ON u.id = a.user_id
INNER JOIN eu_countries e ON u.country = e.country
LEFT JOIN user_stats s ON u.id = s.user_id;

-- Clear logic: Filter active users, EU countries, join stats

WITH cte_name AS (
    SELECT column1, column2
    FROM table
    WHERE condition
)
SELECT *
FROM cte_name;

-- Multiple CTEs
WITH 
    cte1 AS (
        SELECT ...
    ),
    cte2 AS (
        SELECT ...
        FROM cte1  -- Can reference previous CTE
    )
SELECT *
FROM cte2;

Recursive CTE (Hierarchies)

-- Organization chart: Manager → Employee hierarchy
WITH RECURSIVE org_chart AS (
    -- Base case: Top-level managers
    SELECT 
        id,
        name,
        manager_id,
        1 as level,
        CAST(name AS VARCHAR(1000)) as path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive case: Employees under managers
    SELECT 
        e.id,
        e.name,
        e.manager_id,
        oc.level + 1,
        CAST(oc.path || ' -> ' || e.name AS VARCHAR(1000))
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT 
    REPEAT('  ', level - 1) || name as hierarchy,
    level,
    path
FROM org_chart
ORDER BY path;

-- Output:
-- CEO              1  CEO
--   VP Sales       2  CEO -> VP Sales
--     Sales Rep    3  CEO -> VP Sales -> Sales Rep
--   VP Eng         2  CEO -> VP Eng
--     Dev Lead     3  CEO -> VP Eng -> Dev Lead

WITH 
    monthly_sales AS (
        SELECT 
            DATE_TRUNC('month', order_date) as month,
            SUM(total) as revenue
        FROM orders
        WHERE order_date >= '2024-01-01'
        GROUP BY DATE_TRUNC('month', order_date)
    ),
    sales_with_growth AS (
        SELECT 
            month,
            revenue,
            LAG(revenue) OVER (ORDER BY month) as prev_month_revenue,
            revenue - LAG(revenue) OVER (ORDER BY month) as growth
        FROM monthly_sales
    )
SELECT 
    TO_CHAR(month, 'Mon YYYY') as period,
    revenue,
    prev_month_revenue,
    growth,
    ROUND((growth / NULLIF(prev_month_revenue, 0)) * 100, 2) as growth_pct
FROM sales_with_growth
ORDER BY month;

CTE vs Subquery vs Temp Table

"Inherited 500-line query with 8 levels of nested subqueries. Rewrote with CTEs in 2 hours. Same logic, 10x more readable. Junior devs can now understand and modify it."