JOINs, funções de janela e CTEs para fluxos

Consultas a uma única tabela respondem a “o que aconteceu”. A análise de fluxos pergunta mais: quem era a contraparte, quanto valia a transferência naquele momento, qual era o saldo antes e depois e se os fundos foram movimentados rapidamente. Para responder a essas perguntas, são necessários JOINs, funções de janela e expressões de tabela comuns (CTEs), as três ferramentas SQL abordadas nesta aula, além dos erros que mais costumam distorcer os resultados.

As consultas usam o esquema genérico da aula Fundamentos de SQL para tabelas de blockchain: token_transfers (uma linha por transferência de token fungível, com amount_raw em unidades-base), tokens (casas decimais), labels (endereço, entidade, categoria, fonte, confiança, updated_at) e prices (ativo, hora, price_usd).

JOINs e a armadilha da multiplicação de linhas

Um JOIN combina linhas de duas tabelas que correspondem em uma chave. Um INNER JOIN (JOIN) mantém apenas as linhas que correspondem dos dois lados; um LEFT JOIN (LEFT JOIN) mantém todas as linhas da tabela à esquerda e preenche o lado direito com NULLs quando não há correspondência. Para enriquecer dados (adicionar rótulos ou preços às transferências), o LEFT JOIN quase sempre é a escolha certa, porque uma transferência sem rótulo ou sem preço ainda é uma transferência.

O erro de JOIN mais prejudicial é a multiplicação de linhas. Se a tabela à direita tiver várias linhas para a mesma chave, cada linha à esquerda é repetida uma vez para cada correspondência. Tabelas de rótulos são o caso clássico: um endereço de depósito em uma exchange pode ter um rótulo de um serviço de identificação e outro divulgado pela própria exchange. Nos dados de exemplo, um depósito de 100 USDC nesse endereço aparece duas vezes após um JOIN simples, e uma soma das linhas unidas informa 200 USDC. Nada gera erro; o número está simplesmente errado.

Dois hábitos evitam isso. Antes de fazer o JOIN, torne o lado direito único na chave usada no JOIN. Depois, compare a contagem de linhas com a tabela original: um JOIN de enriquecimento nunca deve acrescentar linhas.

Unindo transferências a rótulos

A solução para rótulos duplicados é classificá-los e manter um por endereço.

-- Um rótulo por endereço (maior confiança e, depois, o mais recente)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY confidence DESC, updated_at DESC) AS rn
  FROM labels
)
SELECT tt.transaction_hash,
       tt.to_address,
       COALESCE(l.entity, 'sem rótulo') AS counterparty,
       l.category
FROM token_transfers AS tt
LEFT JOIN best_label AS l
  ON l.address = tt.to_address
 AND l.rn = 1                       -- mantém um rótulo por endereço
WHERE tt.from_address = '0x5e1f…c0de';

A CTE numera os rótulos de cada endereço, do melhor ao pior; o JOIN mantém apenas o número 1. COALESCE substitui um rótulo ausente pela expressão “sem rótulo”, que fica melhor na saída do que NULL.

Observe onde l.rn = 1 está: na cláusula ON, não em WHERE. Em um LEFT JOIN, uma condição na tabela à direita colocada em WHERE é avaliada depois do JOIN, descarta todas as linhas cujo lado direito é NULL e transforma o LEFT JOIN novamente em um INNER JOIN. Todas as contrapartes sem rótulo desapareceriam do resultado, sem que nada na saída alertasse você.

Para rotular as duas pontas de cada transferência, faça o JOIN da mesma CTE duas vezes, com aliases diferentes. Por exemplo: LEFT JOIN best_label AS lf ON lf.address = tt.from_address AND lf.rn = 1 e LEFT JOIN best_label AS lt ON lt.address = tt.to_address AND lt.rn = 1. Agrupar o resultado pelas duas entidades transforma milhares de transferências no nível de endereço em uma tabela curta de fluxos entre entidades, que costuma ser a primeira visão de que um leitor do relatório precisa. Verifique se os endereços são armazenados da mesma forma nos dois lados: um endereço com checksum e letras maiúsculas e minúsculas na tabela de rótulos não corresponde a nada em uma tabela de transferências com letras minúsculas.

Escolher “vence a maior confiança” também é uma decisão analítica. Registre essa decisão e inclua a fonte e a confiança do rótulo no relatório (veja a aula Atribuição e rotulagem). Se os rótulos mudarem com o tempo, use o rótulo que era válido no momento da transferência, não o de hoje.

Unindo dados a preços

Para determinar o valor de uma transferência, é preciso associá-la ao preço do momento correto. Com uma tabela de preços por hora, trunque o timestamp da transferência até a hora e faça o JOIN usando o ativo e a hora.

-- Valor em USD na hora de cada transferência
SELECT tt.transaction_hash,
       tt.amount_raw / POWER(10, t.decimals)               AS amount,
       p.price_usd,
       tt.amount_raw / POWER(10, t.decimals) * p.price_usd AS value_usd
FROM token_transfers AS tt
JOIN tokens AS t
  ON t.token_address = tt.token_address
LEFT JOIN prices AS p                -- LEFT: mantém linhas sem preço
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

Nos dados de exemplo, uma transferência de 500 USDC a um preço de 0,9998 vale 499,90 USD, e 2 WETH a 2.500 valem 5.000 USD. Uma transferência do token imitador “USDC” retorna um preço NULL e um valor NULL. Essa é a resposta correta: não há mercado para esse token, e o NULL informa isso ao leitor. Nunca substitua um preço ausente por zero, nem use como alternativa o preço de outro token com o mesmo símbolo.

JOINs por igualdade precisam de uma linha de preço para cada hora. Quando os preços são esparsos, use o preço anterior mais recente. DuckDB, ClickHouse e Snowflake oferecem ASOF JOIN exatamente para isso; em outros mecanismos, uma função de janela ou uma condição de intervalo faz o mesmo trabalho mais lentamente. A fonte do preço também importa: informe de qual plataforma ou índice ele vem e trate os preços de tokens com pouca liquidez como estimativas que podem ser manipuladas.

Funções de janela: saldos acumulados, primeira e última ocorrência

Uma função de janela calcula um valor para cada linha a partir de um conjunto de linhas relacionadas sem agrupá-las, como faria GROUP BY. A sintaxe é function() OVER (PARTITION BY … ORDER BY … frame): a partição define o grupo, a ordenação define a sequência e o frame define quais linhas são consideradas.

-- Saldo acumulado de um token para um endereço, evento por evento
WITH flows AS (
  SELECT block_number, log_index, block_timestamp, amount_raw AS delta
  FROM token_transfers
  WHERE token_address = '0x7a11…0001' AND to_address = '0x5e1f…c0de'
  UNION ALL
  SELECT block_number, log_index, block_timestamp, -amount_raw
  FROM token_transfers
  WHERE token_address = '0x7a11…0001' AND from_address = '0x5e1f…c0de'
)
SELECT block_timestamp,
       delta,
       SUM(delta) OVER (ORDER BY block_number, log_index
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_raw,
       MIN(block_timestamp) OVER () AS first_seen,
       MAX(block_timestamp) OVER () AS last_seen
FROM flows
ORDER BY block_number, log_index;

A CTE transforma as transferências recebidas em variações positivas e as enviadas em variações negativas; UNION ALL as empilha (um UNION simples removeria linhas idênticas e eliminaria transferências reais). Em seguida, a soma de janela acumula as variações na ordem da blockchain. Nos dados de exemplo, o endereço recebe 2.000 USDC e envia 500, 1.400 e 100, então o saldo fica em 2.000, depois 1.500, depois 100 e, por fim, 0 (em unidades brutas, 2.000.000.000 e assim por diante). MIN e MAX em uma janela vazia OVER () fornecem os horários da primeira e da última ocorrência em cada linha.

Três detalhes são importantes:

• Ordene por número do bloco e índice do log, que juntos dão a posição exata de um evento na blockchain. Os timestamps são iguais dentro de um mesmo bloco.
• Especifique o frame explicitamente. Com ORDER BY e sem frame, o padrão no SQL padrão é RANGE … CURRENT ROW, que trata linhas com chaves de ordenação iguais como uma só e lhes atribui o mesmo total.
• Confira em relação ao estado. Um saldo construído a partir de eventos pressupõe que o token se comporte normalmente. Tokens com taxa sobre transferências e tokens rebasing quebram essa suposição; compare o valor final com balanceOf no mesmo bloco (veja a aula Reconstrução de saldos e históricos).

Outras funções de janela úteis são LAG e LEAD (o valor da linha anterior ou seguinte, por exemplo, o tempo desde a última transferência), ROW_NUMBER (usada acima para os rótulos) e FIRST_VALUE. BigQuery, Snowflake e DuckDB permitem filtrar um resultado de janela com a cláusula QUALIFY; em outros mecanismos, envolva a consulta em uma subconsulta e filtre fora dela.

CTEs para lógica em várias etapas

Uma expressão de tabela comum nomeia um resultado intermediário com WITH name AS (…) para que a etapa seguinte possa usá-lo. As perguntas investigativas naturalmente envolvem várias etapas, e as CTEs mantêm cada etapa legível e fácil de testar. Considere uma hipótese típica: quais endereços receberam um token de um endereço de exploit rotulado e encaminharam pelo menos 90% dele em até 24 horas?

WITH hop1 AS (            -- etapa 1: recebeu de um endereço de exploit rotulado
  SELECT tt.to_address AS addr, SUM(tt.amount_raw) AS amt_in,
         MIN(tt.block_timestamp) AS first_in
  FROM token_transfers AS tt
  WHERE tt.token_address = '0x7a11…0001'
    AND tt.from_address IN (SELECT address FROM labels WHERE category = 'exploit')
  GROUP BY tt.to_address
),
fwd AS (                  -- etapa 2: valor enviado em até 24 horas após o primeiro recebimento
  SELECT h.addr, SUM(tt.amount_raw) AS amt_out
  FROM hop1 AS h
  JOIN token_transfers AS tt
    ON tt.from_address = h.addr AND tt.token_address = '0x7a11…0001'
   AND tt.block_timestamp >= h.first_in
   AND tt.block_timestamp <  h.first_in + INTERVAL '24' HOUR
  GROUP BY h.addr
)
SELECT h.addr, h.first_in, h.amt_in, f.amt_out
FROM hop1 AS h JOIN fwd AS f ON f.addr = h.addr
WHERE f.amt_out >= 0.9 * h.amt_in;      -- encaminhou pelo menos 90%

A etapa 1 encontra os destinatários do primeiro salto. Ela usa IN (subquery) em vez de um JOIN com labels, para que um endereço de exploit com dois rótulos não duplique os valores. A etapa 2 soma o que cada destinatário enviou do mesmo token nas 24 horas após o primeiro recebimento. A etapa final mantém os destinatários que encaminharam pelo menos 90%. Você pode executar cada CTE isoladamente para conferir a contagem de linhas antes de prosseguir.

A sintaxe do intervalo varia: INTERVAL '24' HOUR é SQL padrão e funciona no PostgreSQL, DuckDB e Trino; o BigQuery usa TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Trate o resultado como uma pista, não como uma conclusão: a saída pode incluir fundos que o endereço já detinha, que é justamente a questão metodológica de rastreamento discutida na aula Rastreamento de fundos para frente e para trás. O rastreamento de vários saltos pode ser escrito com WITH RECURSIVE, mas o número de caminhos cresce rapidamente a cada salto; portanto, limite a profundidade ou exporte as arestas para uma ferramenta de grafos.

Foco no exame: diferenças entre INNER JOIN e LEFT JOIN e por que JOINs de enriquecimento devem ser LEFT JOINs; multiplicação de linhas causada por rótulos duplicados e como detectá-la com contagens de linhas; manutenção de um rótulo por endereço com ROW_NUMBER; por que uma condição na tabela à direita em WHERE transforma um LEFT JOIN em INNER JOIN; união de preços pela hora truncada, JOINs ASOF e manutenção de preços ausentes como NULL; saldos acumulados com SUM OVER, UNION ALL de variações com sinal, ordenação por número do bloco e índice do log e frames ROWS explícitos; primeira e última ocorrência com janelas MIN e MAX, LAG e QUALIFY; estruturação de hipóteses em várias etapas com CTEs e semijoins.