JOIN, funzioni finestra e CTE per i flussi

Le query su una singola tabella rispondono a «che cosa è successo». L’analisi dei flussi chiede di più: chi era la controparte, quanto valeva il trasferimento in quel momento, quale fosse il saldo prima e dopo e se i fondi siano stati spostati rapidamente. Per rispondere servono join, funzioni finestra ed espressioni di tabella comuni (CTE), i tre strumenti SQL trattati in questa lezione, insieme agli errori che più spesso alterano i risultati.

Le query usano lo schema generico della lezione Fondamenti di SQL per le tabelle blockchain: token_transfers (una riga per ogni trasferimento di token fungibili, con amount_raw espresso in unità base), tokens (decimali), labels (indirizzo, entità, categoria, fonte, livello di affidabilità, data di aggiornamento) e prices (asset, ora, prezzo in USD).

Join e la trappola della moltiplicazione delle righe

Un join combina righe di due tabelle che corrispondono in base a una chiave. Un inner join (JOIN) conserva solo le righe che corrispondono da entrambi i lati; un left join (LEFT JOIN) conserva tutte le righe della tabella a sinistra e inserisce NULL sul lato destro quando non trova corrispondenze. Per arricchire i dati (aggiungendo etichette o prezzi ai trasferimenti), il left join è quasi sempre la scelta giusta, perché un trasferimento senza etichetta o prezzo è comunque un trasferimento.

L’errore di join più dannoso è la moltiplicazione delle righe. Se la tabella a destra contiene più righe per la stessa chiave, ogni riga a sinistra viene ripetuta una volta per ogni corrispondenza. Le tabelle di etichette sono il caso classico: un indirizzo di deposito di un exchange può avere un’etichetta assegnata da un servizio di tagging e un’altra divulgata dall’exchange stesso. Nei dati di esempio, un deposito di 100 USDC a un indirizzo di questo tipo compare due volte dopo un join semplice e una somma sulle righe unite restituisce 200 USDC. Non si verifica alcun errore: il numero è semplicemente sbagliato.

Due buone pratiche evitano questo problema. Prima del join, rendi univoca la tabella a destra in base alla chiave di join. Dopo il join, confronta il numero di righe con quello della tabella originale: un join di arricchimento non deve mai aggiungere righe.

Unire i trasferimenti alle etichette

Per risolvere il problema delle etichette duplicate, ordinalle e mantienine una sola per indirizzo.

-- Un'etichetta per indirizzo (prima il livello di affidabilità più alto, poi la più 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, 'unlabeled') 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                       -- mantieni un'etichetta per indirizzo
WHERE tt.from_address = '0x5e1f…c0de';

La CTE assegna un numero alle etichette di ogni indirizzo, dalla migliore alla peggiore; il join conserva solo quella con numero 1. COALESCE sostituisce un’etichetta mancante con la parola «unlabeled», che in un risultato si legge meglio di NULL.

Nota dove si trova l.rn = 1: nella clausola ON, non in WHERE. In un left join, una condizione sulla tabella a destra inserita in WHERE viene applicata dopo il join, elimina tutte le righe in cui il lato destro è NULL e trasforma il left join in un inner join. Tutte le controparti senza etichetta scomparirebbero dal risultato, senza che nulla nell’output te lo segnalasse.

Per etichettare entrambi i lati di ogni trasferimento, unisci la stessa CTE due volte usando alias diversi, ad esempio 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. Raggruppando il risultato per le due entità, migliaia di trasferimenti a livello di indirizzo si trasformano in una tabella concisa di flussi da un’entità all’altra, spesso la prima vista di cui ha bisogno chi legge il report. Assicurati che gli indirizzi siano memorizzati nello stesso formato su entrambi i lati: un indirizzo con checksum e lettere maiuscole e minuscole nella tabella delle etichette non corrisponderà ad alcun indirizzo in una tabella dei trasferimenti in minuscolo.

Scegliere «vince il livello di affidabilità più alto» è di per sé una decisione analitica. Documentala e includi nel report la fonte e il livello di affidabilità dell’etichetta (vedi la lezione Attribuzione ed etichettatura). Se le etichette cambiano nel tempo, unisci l’etichetta valida al momento del trasferimento, non quella attuale.

Unire i prezzi

Per attribuire un valore a un trasferimento, occorre abbinarlo al prezzo del momento giusto. Con una tabella dei prezzi orari, tronca il timestamp del trasferimento all’ora e unisci i dati in base all’asset e all’ora.

-- Valore in USD all'ora di ciascun trasferimento
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: conserva le righe senza prezzo
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

Nei dati di esempio, un trasferimento di 500 USDC a un prezzo di 0,9998 vale 499,90 USD, mentre 2 WETH a 2.500 valgono 5.000 USD. Un trasferimento del token imitativo «USDC» restituisce un prezzo NULL e un valore NULL. È la risposta corretta: non esiste un mercato per quel token e il valore NULL lo comunica al lettore. Non sostituire mai un prezzo mancante con zero e non usare mai come ripiego il prezzo di un altro token con lo stesso simbolo.

I join per uguaglianza richiedono una riga di prezzo per ogni ora. Quando i prezzi sono diradati, usa il prezzo precedente più recente. DuckDB, ClickHouse e Snowflake offrono ASOF JOIN proprio per questo; negli altri motori, una funzione finestra o una condizione di intervallo svolge la stessa funzione più lentamente. Anche la fonte del prezzo è importante: specifica da quale piattaforma o indice proviene e considera le quotazioni dei token poco scambiati come stime che possono essere manipolate.

Funzioni finestra: saldi progressivi, prima e ultima occorrenza

Una funzione finestra calcola un valore per ogni riga a partire da un insieme di righe correlate, senza accorparle come farebbe GROUP BY. La sintassi è function() OVER (PARTITION BY … ORDER BY … frame): la partizione definisce il gruppo, l’ordinamento la sequenza e il frame le righe da considerare.

-- Saldo progressivo di un token per un indirizzo, evento per 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;

La CTE trasforma i trasferimenti in entrata in variazioni positive e quelli in uscita in variazioni negative; UNION ALL li accoda (un semplice UNION eliminerebbe le righe identiche e farebbe perdere trasferimenti reali). La somma finestra aggiunge quindi le variazioni nell’ordine della blockchain. Nei dati di esempio, l’indirizzo riceve 2.000 USDC e ne invia 500, 1.400 e 100: il saldo progressivo è 2.000, poi 1.500, poi 100 e infine 0 (in unità raw, 2.000.000.000 e così via). MIN e MAX su una finestra vuota OVER () restituiscono i timestamp della prima e dell’ultima occorrenza per ogni riga.

Tre dettagli sono importanti:

• Ordina per numero di blocco e indice del log, che insieme indicano la posizione esatta di un evento nella blockchain. I timestamp sono uguali all’interno dello stesso blocco.
• Specifica esplicitamente il frame. Se si usa ORDER BY senza indicare un frame, in SQL standard il valore predefinito è RANGE … CURRENT ROW, che considera come un’unica unità le righe con chiavi di ordinamento uguali e assegna loro lo stesso totale.
• Confronta con lo stato. Un saldo ricostruito dagli eventi presuppone che il token si comporti normalmente. I token con commissione sul trasferimento e quelli rebase violano tale presupposto; confronta il valore finale con balanceOf allo stesso blocco (vedi la lezione Ricostruire saldi e cronologie).

Altre funzioni finestra utili sono LAG e LEAD (il valore della riga precedente o successiva, ad esempio il tempo trascorso dall’ultimo trasferimento), ROW_NUMBER (usata sopra per le etichette) e FIRST_VALUE. BigQuery, Snowflake e DuckDB consentono di filtrare un risultato di una funzione finestra con una clausola QUALIFY; negli altri casi, racchiudi la query in una sottoquery e applica il filtro all’esterno.

CTE per logiche a più passaggi

Un’espressione di tabella comune assegna un nome a un risultato intermedio con WITH name AS (…), così che il passaggio successivo possa utilizzarlo. Le domande investigative prevedono naturalmente più passaggi, e le CTE rendono ciascuno di essi leggibile e verificabile. Consideriamo un’ipotesi tipica: quali indirizzi hanno ricevuto un token da un indirizzo di exploit etichettato e ne hanno inoltrato almeno il 90% entro 24 ore?

WITH hop1 AS (            -- passaggio 1: ricevuto da un indirizzo di exploit etichettato
  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 (                  -- passaggio 2: importo inviato entro 24 ore dalla prima ricezione
  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;      -- inoltrato almeno il 90%

Il passaggio 1 individua i destinatari del primo passaggio. Usa IN (subquery) anziché un join a labels, così un indirizzo di exploit con due etichette non può raddoppiare gli importi. Il passaggio 2 somma quanto ogni destinatario ha inviato dello stesso token nelle 24 ore successive alla prima ricezione. Il passaggio finale conserva i destinatari che hanno inoltrato almeno il 90%. Puoi eseguire separatamente ogni CTE per controllarne il numero di righe prima di proseguire.

La sintassi degli intervalli varia: INTERVAL '24' HOUR è SQL standard e funziona in PostgreSQL, DuckDB e Trino; BigQuery usa TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Considera il risultato un’indicazione, non una conclusione: il flusso in uscita può includere fondi già detenuti dall’indirizzo in precedenza, una questione metodologica di tracciamento trattata nella lezione Tracciare i fondi in avanti e all’indietro. Il tracciamento su più passaggi può essere scritto con WITH RECURSIVE, ma il numero di percorsi cresce rapidamente a ogni passaggio: limita la profondità oppure esporta gli archi in uno strumento per grafi.

Focus sull'esame: differenza tra inner join e left join e motivi per cui i join di arricchimento dovrebbero essere left join; moltiplicazione delle righe dovuta a etichette duplicate nei join e come rilevarla contando le righe; mantenere un’etichetta per indirizzo con ROW_NUMBER; perché una condizione sulla tabella a destra in WHERE trasforma un left join in un inner join; unire i prezzi per ora troncata, usare ASOF JOIN e lasciare NULL i prezzi mancanti; saldi progressivi con SUM OVER, UNION ALL di variazioni con segno, ordinamento per numero di blocco e indice del log e frame ROWS espliciti; prima e ultima occorrenza con finestre MIN e MAX, LAG e QUALIFY; strutturare ipotesi a più passaggi con CTE e semi-join.