Window functions: ranking, running totals, lead/lag

-- Sintaxe: função OVER (PARTITION BY ... ORDER BY ... frame)
-- PARTITION BY: divide em grupos (opcional)
-- ORDER BY: ordena dentro de cada partição
-- frame: define quais linhas a janela inclui

-- Exemplo base: funcionários com salário e rank no departamento
SELECT
    nome,
    departamento,
    salario,
    ROW_NUMBER() OVER (
        PARTITION BY departamento
        ORDER BY salario DESC
    ) AS rank_no_dept,
    RANK() OVER (
        PARTITION BY departamento
        ORDER BY salario DESC
    ) AS rank_com_empate,
    AVG(salario) OVER (
        PARTITION BY departamento
    ) AS media_dept,
    salario - AVG(salario) OVER (PARTITION BY departamento) AS diff_da_media
FROM funcionarios;

-- Top 3 vendedores por região (filtrar com subquery)
SELECT *
FROM (
    SELECT
        nome, regiao, vendas,
        RANK() OVER (PARTITION BY regiao ORDER BY vendas DESC) AS rk
    FROM vendedores
) ranked
WHERE rk <= 3;

-- Running total (soma acumulada)
SELECT
    data,
    receita,
    SUM(receita) OVER (ORDER BY data) AS receita_acumulada,
    AVG(receita) OVER (
        ORDER BY data
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS media_movel_7d
FROM receitas_diarias
ORDER BY data;

-- Frames disponíveis:
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  ← padrão para SUM/AVG com ORDER BY
-- ROWS BETWEEN 6 PRECEDING AND CURRENT ROW          ← janela deslizante de 7 dias
-- ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING          ← linha anterior e posterior
-- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ← toda a partição

-- Partição + running total: acumulado por produto por mês
SELECT
    produto,
    mes,
    vendas,
    SUM(vendas) OVER (
        PARTITION BY produto
        ORDER BY mes
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS acumulado_por_produto
FROM vendas_mensais
ORDER BY produto, mes;

-- LAG e LEAD: acessar linhas vizinhas
SELECT
    produto,
    mes,
    vendas,
    LAG(vendas) OVER (PARTITION BY produto ORDER BY mes) AS mes_anterior,
    vendas - LAG(vendas, 1, 0) OVER (
        PARTITION BY produto ORDER BY mes
    ) AS variacao_absoluta,
    ROUND(
        (vendas - LAG(vendas) OVER (PARTITION BY produto ORDER BY mes)) /
        NULLIF(LAG(vendas) OVER (PARTITION BY produto ORDER BY mes), 0) * 100, 2
    ) AS variacao_pct
FROM vendas_mensais
ORDER BY produto, mes;

-- FIRST_VALUE e LAST_VALUE: extremos da janela
SELECT
    vendedor,
    data_venda,
    valor,
    FIRST_VALUE(valor) OVER (
        PARTITION BY vendedor
        ORDER BY data_venda
    ) AS primeira_venda,
    LAST_VALUE(valor) OVER (
        PARTITION BY vendedor
        ORDER BY data_venda
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS ultima_venda   -- ⚠️ precisa do frame explícito!
FROM vendas
ORDER BY vendedor, data_venda;

-- NTH_VALUE, NTILE: percentis e quartis
SELECT
    produto, preco,
    NTILE(4) OVER (ORDER BY preco) AS quartil   -- divide em 4 grupos iguais
FROM produtos;