Querying UTXO data

Bitcoin and other UTXO chains have no account balances to look up: every balance, fee and flow must be rebuilt by linking outputs to the inputs that later spend them. That link is a join, and getting it right in SQL is the foundation for balances, tracing and clustering at scale. This lesson shows how UTXO data is laid out in tables, how to join spends to outputs, and how to turn the heuristics from earlier modules into queries.

The inputs and outputs tables

UTXO datasets split each transaction into rows at two grains. In this module's generic schema (values in satoshis):

An output is identified by its outpoint: the transaction hash plus the output index. An input says which outpoint it spends through spent_transaction_hash and spent_output_index. In this schema, coinbase transactions have no rows in inputs; some datasets keep a coinbase input row with an empty outpoint instead, so check before counting.

A raw Bitcoin input contains only the outpoint, a script and a sequence number; it does not state an address or an amount (see the lesson Raw Bitcoin transactions). The address, value and script_type columns in inputs exist because the ETL pipeline looked up each spent output and copied its fields across, a step the blockchain-etl tools call enrichment. It is a convenience, and a dependency: if enrichment failed for some blocks, inputs there have NULL values, and every fee and balance built on them is wrong. A quick check is to count inputs with NULL value in your range.

Two other layout details catch analysts out:

• Address arrays. Some datasets, including the BigQuery Bitcoin dataset, store addresses as an array, because a few script types (bare multisig) contain several keys and non-standard scripts contain none. To get one row per address, unnest the array: CROSS JOIN UNNEST(addresses) AS address in BigQuery, CROSS JOIN UNNEST(addresses) AS t(address) in Trino and DuckDB, unnest() in PostgreSQL, LATERAL FLATTEN in Snowflake and explode() in Spark SQL. The same syntax unnests inputs and outputs stored as nested records inside a transactions table. Outputs with no address, such as OP_RETURN data outputs, keep a NULL or an empty array.
• Duplicate txids. Two coinbase transactions from 2010 have the same txid as earlier coinbase transactions, the problem BIP 30 later prohibited. A join on transaction hash alone mismatches those few rows; when exactness matters, key on hash and block number.

Joining spends to outputs

The join from an input to the output it spends is the edge of the UTXO spending graph (see the lesson The UTXO model for analysts):

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

Both conditions are needed. Joining on the hash alone pairs each input with every output of the earlier transaction.

An output that no input spends is an unspent transaction output. Finding UTXOs is therefore an anti-join: outputs left-joined to inputs, keeping rows where no input matched.

-- Unspent outputs of one address as of block 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     -- spends up to the snapshot only
WHERE o.address = 'bc1q…7k3f'
  AND o.block_number <= 870000
  AND i.transaction_hash IS NULL;           -- no spending input found

The query keeps outputs to the address created at or before block 870,000 that were not spent at or before that block. Summing value gives the address's balance at that height. In the sample, the address received 0.05 and 0.03 BTC; the first output was spent at block 870,000 and the second at 870,005, so the balance at 870,000 is 0.03 BTC.

The snapshot condition on i.block_number must sit in the ON clause. Moved to WHERE, it is evaluated after the join: for unmatched outputs i.block_number is NULL, the condition fails, and the query returns nothing at all. On the sample it returns zero rows instead of the correct 0.03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) is an equivalent way to write the anti-join that avoids the trap.

Computing balances and fees

A balance history follows the pattern from the lesson Joins, window functions and CTEs for flows: take outputs to the address as positive amounts and inputs from the address as negative amounts, stack them with UNION ALL, and run a window sum in block order. Because the enriched inputs table carries the spent output's address, no join is needed for this.

A transaction's fee is the sum of its inputs minus the sum of its outputs. The obvious query, joining inputs to outputs on transaction hash and summing both, is wrong: a transaction with 3 inputs and 4 outputs produces 12 joined rows, and both sums are multiplied. Aggregate each side first, then join the totals.

-- Fee per transaction: aggregate each side, then join
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          -- coinbase has no inputs: dropped by the join
FROM vout
JOIN vin ON vin.transaction_hash = vout.transaction_hash
ORDER BY fee_sat DESC;

In the sample block, one transaction spends 5,000,000 and 2,000,000 satoshis and creates outputs of 6,000,000 and 991,000, so its fee is 9,000 satoshis. The coinbase transaction has no inputs and disappears in the inner join, which is right: it pays no fee. To get a fee rate, divide by the virtual size from the transactions table. Many datasets also precompute fee; recomputing it on a sample is a good check of their enrichment.

Change detection in SQL

The heuristics in the lesson Change detection heuristics translate into SQL as flags: columns that mark candidates, not conclusions. One of the stronger signals is a script-type match: in a two-output transaction where all inputs share one script type, an output of that same type is a change candidate if the other output is of a different type.

-- Change candidates: the only output whose script type matches the inputs
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      -- all inputs share one type
),
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;

The first CTE keeps transactions whose inputs all have one script type. The second marks each output that matches it and counts outputs per transaction with a window. The outer query keeps two-output transactions with exactly one matching output, and adds a weaker second signal: whether the amount is not a round multiple of 100,000 satoshis. In the sample, the native SegWit inputs pay 6,000,000 satoshis to a P2SH address and 991,000 to a new native SegWit address; the 991,000 output is flagged on both counts.

Store flags in a table with the heuristic's name and version, and combine them into a confidence level in the analysis, never in a hard-coded join. Script-type matching fails when the recipient uses the same wallet type, which is increasingly common as Taproot spreads, and when wallets deliberately vary change types.

Seeding clusters

The common-input ownership heuristic (see the lesson The common-input ownership heuristic) links all input addresses of a transaction. In SQL that becomes an edge list: pairs of addresses that co-spent.

-- Common-input edges, skipping CoinJoin-like transactions
WITH equal_outs AS (                    -- largest group of equal-value outputs
  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                 -- skip CoinJoin-like transactions
)
SELECT DISTINCT anchor AS addr_a, address AS addr_b, transaction_hash
FROM ins
WHERE address <> anchor;                -- one edge per extra input address

The first CTE finds, per transaction, the largest number of outputs with the same value; three or more equal outputs is a crude CoinJoin signal, and those transactions are excluded. The second links every input address to one anchor (the smallest address in the transaction), so a transaction with n input addresses yields n − 1 edges instead of every pair. Keeping the transaction hash on each edge preserves the evidence for the report.

Turning edges into clusters means finding connected components, which is iterative. Analysts usually export the edge list to a graph library with a union-find algorithm. Within SQL, label propagation works: give each address its own ID, repeatedly replace it with the smallest ID among its neighbors, and stop when nothing changes. WITH RECURSIVE can express this, but on full-chain data it is slow. Whatever the method, watch for superclusters, record the exclusion rules and their version, and evaluate the result (see the lesson Evaluating cluster quality).

Exam focus: the outpoint (transaction hash plus output index) as the join key between inputs and outputs; input enrichment and its failure mode; unnesting address arrays and the main syntax differences; finding UTXOs and point-in-time balances with an anti-join, with the snapshot condition in the ON clause; computing fees by aggregating inputs and outputs separately to avoid join fan-out; coinbase transactions and BIP 30 duplicate txids; change detection as stored flags with versions; building common-input edge lists with CoinJoin exclusions and computing clusters as connected components.