SQL para Analistas de Dados: Window Functions, CTEs e Consultas Avancadas

Window functions, CTEs (Common Table Expressions) e padroes avancados de consulta formam a espinha dorsal do SQL analitico. Seja para uma entrevista de analista de dados ou para resolver problemas complexos de reporting, essas tecnicas transformam subqueries verbosas e dificeis de ler em SQL limpo, performatico e sustentavel.

Entendendo Window Functions e a Clausula OVER

Uma window function aplica um calculo sobre uma "janela" definida de linhas. A clausula OVER controla quais linhas fazem parte dessa janela e como elas sao ordenadas. Diferente de funcoes agregadas com GROUP BY, window functions preservam cada linha individual no resultado.

A sintaxe geral segue um padrao consistente em todos os principais bancos de dados: PostgreSQL, MySQL 8+, BigQuery, SQL Server e Snowflake.

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

O PARTITION BY divide os dados em grupos (similar ao GROUP BY, mas sem colapsar as linhas). O ORDER BY define a sequencia dentro de cada particao. A clausula de frame opcional (ROWS BETWEEN ...) restringe a janela a um intervalo especifico de linhas.

Esse padrao e recorrente em sql interview questions, onde o candidato precisa demonstrar dominio sobre a diferenca entre agregacoes tradicionais e funcoes de janela.

ROW_NUMBER, RANK e DENSE_RANK: Diferencas Praticas

Essas tres funcoes de ranking parecem semelhantes, mas se comportam de forma distinta quando existem empates. Escolher a funcao errada e uma fonte frequente de bugs em consultas analiticas.

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

O ROW_NUMBER sempre atribui um inteiro sequencial unico, mesmo em caso de empate: uma das linhas recebe arbitrariamente o numero menor. O RANK atribui o mesmo numero para empates, mas pula os numeros subsequentes (1, 1, 3). O DENSE_RANK tambem trata empates, mas nunca pula numeros (1, 1, 2). Para consultas top-N em que valores iguais devem compartilhar a mesma posicao, o DENSE_RANK e geralmente a escolha correta.

Esse tipo de comparacao aparece com frequencia em advanced sql queries de processos seletivos, pois testa a compreensao de nuances que impactam diretamente os resultados de analises.

LAG e LEAD: Analise Periodo a Periodo

LAG e LEAD acessam dados de linhas anteriores ou subsequentes sem a necessidade de self-joins. Essas funcoes sao essenciais para calcular crescimento periodo a periodo, detectar tendencias e identificar variacoes.

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

O segundo argumento de LAG/LEAD especifica o deslocamento (o padrao e 1). Um terceiro argumento opcional define um valor default quando nao existe linha naquela posicao, o que e util para evitar NULLs na primeira ou ultima linha. O NULLIF no calculo de crescimento previne erros de divisao por zero.

Em entrevistas para sql for data analyst, perguntas sobre analise mes a mes sao extremamente comuns. Dominar LAG e LEAD elimina a necessidade de self-joins complexos e torna a consulta significativamente mais eficiente.

NTILE e Segmentacao por Percentis

O NTILE distribui linhas em um numero especificado de grupos aproximadamente iguais. Analistas de dados utilizam essa funcao para analises de quartis, scoring por decis e segmentacao de clientes.

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

O quartil 1 contem os maiores gastadores; o quartil 4, os menores. Esse padrao mapeia diretamente para a segmentacao RFM (Recencia, Frequencia, Valor Monetario) utilizada em analytics de marketing. E um dos padroes mais aplicados no dia a dia de equipes de dados.

Common Table Expressions: Substituindo Subqueries Aninhadas

CTEs (clausulas WITH) dividem consultas complexas em etapas nomeadas e legiveis. Cada CTE funciona como um resultado temporario que existe apenas durante a execucao da query. Alem de melhorar a legibilidade, CTEs facilitam o debug, pois cada etapa pode ser testada de forma independente.

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

Essa abordagem em tres etapas substitui o que seria uma subquery profundamente aninhada. Cada CTE tem uma responsabilidade clara: agregacao, ranking e filtragem. Quando combinadas com sql window functions, CTEs formam a base de praticamente qualquer relatorio analitico robusto.

CTEs Recursivas para Dados Hierarquicos

CTEs recursivas resolvem problemas envolvendo dados hierarquicos ou em formato de grafo: organogramas, arvores de categorias, listas de materiais (BOM) e consultas de caminho.

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

O caso base seleciona os nos raiz (funcionarios sem gerente). O caso recursivo faz um join de volta com a propria CTE, construindo a hierarquia nivel a nivel. A coluna management_chain concatena nomes para exibir o caminho completo de reporte. A maioria dos bancos limita a profundidade de recursao para evitar loops infinitos. No PostgreSQL, o padrao e 100 iteracoes.

Padroes Analiticos Avancados: Gaps, Islands e Totais Acumulados

O padrao gaps-and-islands identifica sequencias consecutivas em dados: periodos de assinatura ativa, dias consecutivos de login ou series de producao ininterruptas. Essa tecnica combina ROW_NUMBER com aritmetica de datas.

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

O insight central: para datas consecutivas, subtrair um numero de linha incremental da data sempre resulta no mesmo valor. Quando ocorre uma lacuna, o valor resultante muda, criando um novo grupo. A consulta acima encontra todas as sequencias de 3 ou mais dias consecutivos de login por usuario.

Esse padrao e um dos favoritos em sql interview questions avancadas, pois exige raciocinio logico e dominio simultaneo de CTEs e window functions.

Combinando Window Functions com CASE para Analytics Condicionais

Consultas analiticas do mundo real frequentemente combinam window functions com expressoes CASE para calcular metricas condicionais dentro da mesma query.

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Essa unica consulta calcula uma media movel de 7 dias, uma soma acumulada trimestral que reinicia a cada quarter e uma flag de outlier, tudo sem subqueries ou self-joins. Esse tipo de composicao demonstra maturidade analitica e e altamente valorizado em processos seletivos.

Otimizacao de Performance em Consultas Analiticas

Window functions e CTEs sao ferramentas poderosas, mas podem se tornar gargalos em tabelas com grandes volumes. Algumas tecnicas ajudam a manter a performance em escala.

Primeiro, indexar as colunas usadas em PARTITION BY e ORDER BY. Um indice composto que corresponda a definicao da janela elimina operacoes de ordenacao.

Segundo, filtrar dados antes de aplicar window functions. Colocar condicoes na clausula WHERE (antes da avaliacao das window functions) reduz o volume de dados que a janela precisa processar.

Terceiro, evitar definicoes de janela redundantes. Named windows reduzem repeticao e sinalizam ao otimizador que multiplas funcoes compartilham o mesmo frame:

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

A clausula WINDOW (suportada no PostgreSQL, MySQL 8+ e BigQuery) define a janela uma unica vez e a reutiliza em multiplas funcoes. Isso melhora tanto a legibilidade quanto a potencial otimizacao do plano de execucao.

Conclusao

• Window functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) operam sobre conjuntos de linhas sem colapsa-las, diferente de agregacoes com GROUP BY
• CTEs dividem consultas complexas em etapas nomeadas e testaveis, substituindo subqueries profundamente aninhadas
• CTEs recursivas tratam dados hierarquicos como organogramas e arvores de categorias, mas exigem limites de profundidade e colunas de join indexadas
• O padrao gaps-and-islands (ROW_NUMBER + aritmetica de datas) identifica sequencias consecutivas em dados de series temporais
• Named windows (clausula WINDOW) reduzem duplicacao de codigo e podem melhorar a otimizacao do plano de consulta
• Filtrar dados em clausulas WHERE antes da avaliacao das window functions e fundamental para manter performance em grandes tabelas
• Esses padroes aparecem frequentemente em entrevistas de data analytics e se aplicam diretamente ao dia a dia de reporting, segmentacao e analise de tendencias