Interrogare i dati UTXO

Bitcoin e le altre blockchain UTXO non hanno saldi di conto da consultare: ogni saldo, commissione e flusso deve essere ricostruito collegando gli output agli input che li spendono in seguito. Questo collegamento è un join, e realizzarlo correttamente in SQL è il fondamento per calcolare saldi, tracciare i flussi e creare cluster su larga scala. Questa lezione illustra come sono organizzati i dati UTXO nelle tabelle, come collegare le spese agli output e come trasformare le euristiche dei moduli precedenti in query.

Le tabelle degli input e degli output

I dataset UTXO suddividono ogni transazione in righe con due diversi livelli di dettaglio. Nello schema generico di questo modulo (valori in satoshi):

Un output è identificato dal suo outpoint: l'hash della transazione più l'indice dell'output. Un input indica quale outpoint spende tramite spent_transaction_hash e spent_output_index. In questo schema, le transazioni coinbase non hanno righe in inputs; alcuni dataset includono invece una riga di input coinbase con un outpoint vuoto, quindi verifica il formato prima di effettuare conteggi.

Un input Bitcoin grezzo contiene solo l'outpoint, uno script e un numero di sequenza; non specifica un indirizzo né un importo (vedi la lezione Transazioni Bitcoin grezze). Le colonne address, value e script_type di inputs sono presenti perché la pipeline ETL ha cercato ciascun output speso e ne ha copiato i campi, un passaggio che gli strumenti blockchain-etl chiamano arricchimento. È una comodità, ma anche una dipendenza: se l'arricchimento non è riuscito per alcuni blocchi, gli input corrispondenti hanno valori NULL e tutte le commissioni e i saldi calcolati a partire da essi sono errati. Un controllo rapido consiste nel contare gli input con value NULL nell'intervallo considerato.

Altri due dettagli di layout possono trarre in inganno gli analisti:

• Array di indirizzi. Alcuni dataset, incluso il dataset Bitcoin di BigQuery, memorizzano addresses come array, perché alcuni tipi di script (multisig senza restrizioni) contengono più chiavi e gli script non standard non ne contengono nessuna. Per ottenere una riga per indirizzo, espandi l'array: CROSS JOIN UNNEST(addresses) AS address in BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) in Trino e DuckDB, unnest() in PostgreSQL, LATERAL FLATTEN in Snowflake ed explode() in Spark SQL. La stessa sintassi espande gli input e gli output memorizzati come record annidati all'interno di una tabella transactions. Gli output senza indirizzo, come gli output di dati OP_RETURN, mantengono un valore NULL o un array vuoto.
• Txid duplicati. Due transazioni coinbase del 2010 hanno lo stesso txid di transazioni coinbase precedenti: il problema che BIP 30 ha poi vietato. Un join basato solo sull'hash della transazione abbina in modo errato quelle poche righe; quando è necessaria la massima precisione, usa come chiave l'hash e il numero del blocco.

Collegare le spese agli output

Il join da un input all'output che spende costituisce l'arco del grafo delle spese UTXO (vedi la lezione Il modello UTXO per gli analisti):

inputs.spent_transaction_hash = outputs.transaction_hash AND inputs.spent_output_index = outputs.output_index

Sono necessarie entrambe le condizioni. Un join basato solo sull'hash abbina ogni input a tutti gli output della transazione precedente.

Un output che non viene speso da alcun input è un output di transazione non speso. Per trovare gli UTXO si usa quindi un anti-join: si uniscono gli output agli input con un left join, mantenendo le righe per cui non è stato trovato alcun input corrispondente.

-- Output non spesi di un indirizzo al blocco 870.000
SELECT o.transaction_hash, o.output_index, o.value / 1e8 AS btc
FROM outputs AS o
LEFT JOIN inputs AS i
  ON  i.spent_transaction_hash = o.transaction_hash
  AND i.spent_output_index     = o.output_index
  AND i.block_number          <= 870000     -- considera solo le spese fino all'istantanea
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- nessun input di spesa trovato

La query mantiene gli output dell'indirizzo creati al blocco 870.000 o prima che non siano stati spesi entro quel blocco. La somma di value dà il saldo dell'indirizzo a quell'altezza. Nell'esempio, l'indirizzo ha ricevuto 0,05 e 0,03 BTC; il primo output è stato speso al blocco 870.000 e il secondo al blocco 870.005, quindi il saldo al blocco 870.000 è 0,03 BTC.

La condizione sull'istantanea relativa a i.block_number deve trovarsi nella clausola ON. Se viene spostata in WHERE, viene valutata dopo il join: per gli output senza corrispondenza, i.block_number è NULL, la condizione non è soddisfatta e la query non restituisce nulla. Nell'esempio, restituisce zero righe invece del valore corretto di 0,03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) è un modo equivalente di scrivere l'anti-join che evita questa insidia.

Calcolare saldi e commissioni

Una cronologia del saldo segue il modello della lezione Join, funzioni finestra e CTE per i flussi: si prendono gli output diretti all'indirizzo come importi positivi e gli input provenienti dall'indirizzo come importi negativi, li si accorpa con UNION ALL e si calcola una somma finestra in ordine di blocco. Poiché la tabella inputs arricchita contiene l'indirizzo dell'output speso, non è necessario alcun join.

La commissione di una transazione è la somma dei suoi input meno la somma dei suoi output. La query più ovvia, che unisce input e output in base all'hash della transazione e li somma entrambi, è errata: una transazione con 3 input e 4 output produce 12 righe unite, e entrambe le somme vengono moltiplicate. Prima aggrega ciascun lato, poi unisci i totali.

-- Commissione per transazione: aggrega ciascun lato, poi unisci
WITH vin AS (
  SELECT transaction_hash, COUNT(*) AS n_in, SUM(value) AS in_sat
  FROM inputs WHERE block_number = 870000
  GROUP BY transaction_hash
),
vout AS (
  SELECT transaction_hash, COUNT(*) AS n_out, SUM(value) AS out_sat
  FROM outputs WHERE block_number = 870000
  GROUP BY transaction_hash
)
SELECT vout.transaction_hash, n_in, n_out,
       in_sat - out_sat AS fee_sat          -- la coinbase non ha input: viene esclusa dal join
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

Nel blocco dell'esempio, una transazione spende 5.000.000 e 2.000.000 satoshi e crea output da 6.000.000 e 991.000, quindi la sua commissione è di 9.000 satoshi. La transazione coinbase non ha input e scompare nel join interno, come è corretto: non paga commissioni. Per ottenere il tasso della commissione, dividi per la dimensione virtuale indicata nella tabella transactions. Molti dataset calcolano in anticipo anche fee; ricalcolarla su un campione è un buon modo per verificare il loro arricchimento.

Rilevare il resto in SQL

Le euristiche della lezione Euristiche per rilevare il resto si traducono in SQL in flag: colonne che identificano candidati, non conclusioni. Uno dei segnali più forti è la corrispondenza del tipo di script: in una transazione con due output, in cui tutti gli input condividono un tipo di script, un output dello stesso tipo è un candidato come resto se l'altro output è di un tipo diverso.

-- Candidati come resto: l'unico output il cui tipo di script corrisponde a quello degli input
WITH tx_in AS (
  SELECT transaction_hash, MIN(script_type) AS in_type
  FROM inputs WHERE block_number BETWEEN 870000 AND 870099
  GROUP BY transaction_hash
  HAVING COUNT(DISTINCT script_type) = 1      -- tutti gli input condividono un tipo
),
outs AS (
  SELECT o.transaction_hash, o.output_index, o.address, o.value,
         CASE WHEN o.script_type = t.in_type THEN 1 ELSE 0 END AS type_match,
         COUNT(*) OVER (PARTITION BY o.transaction_hash) AS n_out
  FROM outputs AS o JOIN tx_in AS t ON t.transaction_hash = o.transaction_hash
  WHERE o.block_number BETWEEN 870000 AND 870099
)
SELECT transaction_hash, output_index, address, value,
       CASE WHEN MOD(value, 100000) <> 0 THEN 1 ELSE 0 END AS not_round
FROM (SELECT outs.*, SUM(type_match) OVER (PARTITION BY transaction_hash) AS n_match
      FROM outs) AS x
WHERE n_out = 2 AND n_match = 1 AND type_match = 1;

La prima CTE mantiene le transazioni i cui input hanno tutti un unico tipo di script. La seconda contrassegna ogni output che corrisponde a quel tipo e conta gli output per transazione con una funzione finestra. La query esterna mantiene le transazioni con due output e un solo output corrispondente, quindi aggiunge un secondo segnale, più debole: se l'importo non è un multiplo tondo di 100.000 satoshi. Nell'esempio, gli input SegWit nativi inviano 6.000.000 satoshi a un indirizzo P2SH e 991.000 a un nuovo indirizzo SegWit nativo; l'output da 991.000 satoshi viene contrassegnato da entrambi i segnali.

Memorizza i flag in una tabella insieme al nome e alla versione dell'euristica, e combina i flag in un livello di confidenza nell'analisi, mai in un join codificato in modo rigido. La corrispondenza del tipo di script non funziona quando il destinatario usa lo stesso tipo di wallet, un caso sempre più comune con la diffusione di Taproot, né quando i wallet variano deliberatamente i tipi di resto.

Creare cluster iniziali

L'euristica della proprietà comune degli input (vedi la lezione L'euristica della proprietà comune degli input) collega tutti gli indirizzi di input di una transazione. In SQL questo si traduce in un elenco di archi: coppie di indirizzi che hanno speso insieme.

-- Archi con input comuni, escludendo le transazioni simili a CoinJoin
WITH equal_outs AS (                    -- gruppo più numeroso di output di pari valore
  SELECT transaction_hash, MAX(n) AS max_equal
  FROM (SELECT transaction_hash, value, COUNT(*) AS n
        FROM outputs WHERE block_number BETWEEN 870000 AND 870099
        GROUP BY transaction_hash, value) AS g
  GROUP BY transaction_hash
),
ins AS (
  SELECT i.transaction_hash, i.address,
         MIN(i.address) OVER (PARTITION BY i.transaction_hash) AS anchor
  FROM inputs AS i JOIN equal_outs AS e ON e.transaction_hash = i.transaction_hash
  WHERE i.block_number BETWEEN 870000 AND 870099
    AND e.max_equal < 3                 -- escludi le transazioni simili a CoinJoin
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- un arco per ogni indirizzo di input aggiuntivo

La prima CTE individua, per ogni transazione, il numero massimo di output con lo stesso valore; tre o più output di pari valore sono un segnale approssimativo di CoinJoin e queste transazioni vengono escluse. La seconda collega ogni indirizzo di input a un punto di riferimento (l'indirizzo più piccolo della transazione), così una transazione con n indirizzi di input produce n − 1 archi invece di ogni possibile coppia. Mantenere l'hash della transazione su ciascun arco conserva le evidenze per il report.

Trasformare gli archi in cluster significa trovare le componenti connesse, un processo iterativo. Di solito gli analisti esportano l'elenco di archi in una libreria per grafi dotata di un algoritmo union-find. In SQL è possibile usare la propagazione delle etichette: assegna a ogni indirizzo il proprio ID, sostituiscilo ripetutamente con l'ID più piccolo tra quelli dei suoi vicini e fermati quando non cambia più nulla. WITH RECURSIVE può esprimere questo processo, ma è lento sui dati di un'intera blockchain. Qualunque sia il metodo, fai attenzione ai supercluster, registra le regole di esclusione e la loro versione e valuta il risultato (vedi la lezione Valutare la qualità dei cluster).

Focus sull'esame: l'outpoint (hash della transazione più indice dell'output) come chiave di join tra input e output; l'arricchimento degli input e il suo possibile fallimento; l'espansione degli array di indirizzi e le principali differenze di sintassi; l'individuazione degli UTXO e dei saldi a un dato momento con un anti-join, inserendo la condizione sull'istantanea nella clausola ON; il calcolo delle commissioni aggregando separatamente input e output per evitare l'espansione delle righe del join; le transazioni coinbase e i txid duplicati di BIP 30; il rilevamento del resto tramite flag memorizzati con le rispettive versioni; la creazione di elenchi di archi con input comuni, escludendo le transazioni CoinJoin, e il calcolo dei cluster come componenti connesse.