Subqueries e CTEs

Subquery no WHERE

-- Clientes que gastaram mais que a média
SELECT c.nome, SUM(p.total) AS total_gasto
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nome
HAVING SUM(p.total) > (SELECT AVG(total) FROM pedidos);

-- EXISTS (mais performático que IN para subqueries grandes)
SELECT nome FROM clientes c
WHERE EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = c.id
    AND p.total > 1000
);

-- NOT EXISTS (clientes sem pedidos)
SELECT nome FROM clientes c
WHERE NOT EXISTS (
    SELECT 1 FROM pedidos p
    WHERE p.cliente_id = c.id
);

-- IN vs EXISTS
-- IN: SELECT nome FROM clientes WHERE id IN (SELECT cliente_id FROM pedidos)
-- EXISTS: melhor performance com subquery grande

Subquery no FROM

-- Subquery como tabela derivada (precisa de alias)
SELECT departamento, media_salarial
FROM (
    SELECT 
        departamento,
        AVG(salario) AS media_salarial,
        COUNT(*) AS total_func
    FROM funcionarios
    GROUP BY departamento
) AS dept_stats
WHERE total_func > 5
ORDER BY media_salarial DESC;

Subquery no SELECT

SELECT 
    c.nome,
    (SELECT COUNT(*) FROM pedidos p WHERE p.cliente_id = c.id) AS total_pedidos,
    (SELECT SUM(p.total) FROM pedidos p WHERE p.cliente_id = c.id) AS total_gasto,
    (SELECT AVG(total) FROM pedidos) AS media_geral,
    (SELECT SUM(p.total) FROM pedidos p WHERE p.cliente_id = c.id) 
        / NULLIF((SELECT AVG(total) FROM pedidos), 0) * 100 AS porcentagem_media
FROM clientes c;

CTE — Common Table Expression

-- WITH statement: organiza queries complexas
WITH vendas_por_mes AS (
    SELECT 
        DATE_TRUNC('month', data) AS mes,
        SUM(valor) AS receita,
        COUNT(*) AS transacoes
    FROM vendas
    WHERE data >= '2024-01-01'
    GROUP BY mes
),
variacao AS (
    SELECT 
        mes,
        receita,
        LAG(receita) OVER (ORDER BY mes) AS receita_anterior,
        (receita - LAG(receita) OVER (ORDER BY mes)) / 
            NULLIF(LAG(receita) OVER (ORDER BY mes), 0) * 100 AS crescimento
    FROM vendas_por_mes
)
SELECT * FROM variacao ORDER BY mes;

-- CTE recursiva (hierarquia)
WITH RECURSIVE subordinados AS (
    -- Base case
    SELECT id, nome, gerente_id, 1 AS nivel
    FROM funcionarios
    WHERE gerente_id IS NULL
    
    UNION ALL
    
    -- Recursive case
    SELECT f.id, f.nome, f.gerente_id, s.nivel + 1
    FROM funcionarios f
    JOIN subordinados s ON f.gerente_id = s.id
)
SELECT * FROM subordinados ORDER BY nivel, nome;

Lab: Análise Avançada

-- Top 10% clientes (NTILE)
WITH clientes_rank AS (
    SELECT 
        c.id,
        c.nome,
        SUM(p.total) AS total_gasto,
        NTILE(10) OVER (ORDER BY SUM(p.total) DESC) AS decil
    FROM clientes c
    LEFT JOIN pedidos p ON p.cliente_id = c.id
    GROUP BY c.id, c.nome
)
SELECT * FROM clientes_rank WHERE decil = 1;

-- Clientes que aumentaram gasto
WITH gasto_por_mes AS (
    SELECT 
        c.id,
        c.nome,
        DATE_TRUNC('month', p.data_pedido) AS mes,
        SUM(p.total) AS gasto
    FROM clientes c
    JOIN pedidos p ON p.cliente_id = c.id
    GROUP BY c.id, c.nome, mes
),
variacao AS (
    SELECT 
        *,
        LAG(gasto) OVER (PARTITION BY id ORDER BY mes) AS gasto_anterior,
        CASE 
            WHEN gasto > LAG(gasto) OVER (PARTITION BY id ORDER BY mes) 
            THEN 'aumentou' 
            ELSE 'diminuiu' 
        END AS tendencia
    FROM gasto_por_mes
)
SELECT nome, mes, gasto, gasto_anterior, tendencia
FROM variacao
WHERE tendencia = 'aumentou';

Subqueries: WHERE (filtro), FROM (tabela derivada), SELECT (coluna calculada). CTEs são mais legíveis que subqueries aninhadas. CTEs recursivas são ideais para hierarquias e grafos.