Querying UTXO data and EVM logs and traces

Most of what an investigator needs from a blockchain is not stored as a ready answer. On Bitcoin and other UTXO chains there are no balances to look up: every balance, fee and flow must be rebuilt by joining outputs to the inputs that later spend them. On EVM chains, token movements, approvals and contract payouts sit in event logs and internal calls rather than in the transaction itself. Decoded tables cover popular contracts, but sooner or later a case involves a new token, an unverified contract or a pattern nobody has decoded. This lesson shows how to write those joins and decodings in SQL correctly, so that each figure in a report rests on a query that another examiner can rerun and get the same result. It uses the generic schema of Lesson 2.7 and builds on the join, window and CTE patterns of Lesson 2.8.

UTXO tables and the outpoint join

UTXO datasets split each transaction into rows at two grains (values in satoshis):

An output is identified by its outpoint: transaction hash plus output index. An input names the 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 with an empty outpoint, so check before counting.

A raw input contains only the outpoint, a script and a sequence number, with no address or amount (Lesson 2.2). The address, value and script_type columns in inputs exist because the 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, those inputs have NULL values and every fee and balance built on them is wrong. Count inputs with NULL value in your range before relying on it.

Two more layout traps:

• Address arrays. Some datasets, including the BigQuery Bitcoin dataset, store addresses as an array, because bare multisig scripts contain several keys and non-standard scripts none. To get one row per address, unnest it: 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, explode() in Spark SQL. OP_RETURN outputs keep a NULL or empty array.
• Duplicate txids. Two coinbase transactions from 2010 share the txid of earlier coinbase transactions, a problem BIP 30 later prohibited. Where exactness matters, key on hash and block number.

The edge of the UTXO spending graph is the join on both parts of the outpoint:

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

Joining on the hash alone pairs each input with every output of the earlier transaction.

Unspent outputs and point-in-time balances

Finding UTXOs is an anti-join: left-join outputs to inputs and keep 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

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, when i.block_number is NULL for every unmatched output, so the query returns zero rows instead of 0.03 BTC. NOT EXISTS (SELECT 1 FROM inputs …) expresses the same anti-join without the trap. For a balance history, stack outputs to the address as positive amounts and enriched inputs from it as negative amounts with UNION ALL, then run a window sum in block order (Lesson 2.8).

Fees without join fan-out

A fee is the sum of inputs minus the sum of outputs. 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 multiplies both sums. 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 sat and creates outputs of 6,000,000 and 991,000, so its fee is 9,000 sat. The coinbase disappears in the inner join, which is right: it pays no fee. Divide by virtual_size for a fee rate. Many datasets precompute fee; recomputing it on a sample is a good test of their enrichment.

Heuristics as stored flags

Heuristics from Module 4 become flags: columns that mark candidates, not conclusions. One stronger change signal (Lesson 4.2) is a script-type match: in a two-output transaction whose inputs share one script type, the only output of that type is a change candidate.

-- 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 outer query also adds a weaker signal: an amount that is not a round multiple of 100,000 sat. In the sample, native SegWit inputs pay 6,000,000 sat to a P2SH address and 991,000 to a new native SegWit address; the 991,000 output is flagged on both counts. Store flags 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, increasingly common as Taproot spreads, and when wallets deliberately vary change types.

The common-input ownership heuristic (Lesson 4.1) becomes an edge list 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

Three or more equal outputs is a crude CoinJoin signal, so those transactions are excluded. Linking every input address to one anchor (the smallest address in the transaction) yields n − 1 edges for n addresses instead of every pair, and keeping the transaction hash on each edge preserves the evidence. Turning edges into clusters means finding connected components, which is iterative: analysts usually export the edges to a graph library with a union-find algorithm. In SQL, label propagation (repeatedly replace each address's ID with the smallest among its neighbors until nothing changes) can be written with WITH RECURSIVE, but is slow on full-chain data. Record exclusion rules and their version, and watch for superclusters (Lesson 4.4).

EVM logs: signatures, topics and data

A log has the emitting contract address, up to four topics and a data field. For a normal Solidity event, topic0 is the Keccak-256 hash of the canonical signature (name and parameter types, no spaces or names). Parameters declared indexed go into topic1 to topic3; the rest are ABI-encoded into data as 32-byte words.

Filtering on topic0 selects one kind of event across all contracts. Add contract_address whenever you mean one token or protocol: anyone can deploy a contract that emits a well-formed Transfer, and scam tokens do exactly that to plant fake transfers in victims' histories. An event is only as trustworthy as the contract that emitted it. ERC-20 and ERC-721 share the Transfer signature, but ERC-721 indexes the token ID, giving four topics; testing topic3 IS NULL keeps ERC-20 transfers. Where topics are stored as an array, BigQuery counts from zero (topics[OFFSET(0)] or SAFE_OFFSET(0)) while PostgreSQL, Trino and DuckDB count from one (topics[1]); getting this wrong shifts every topic silently.

Topics and data words are 64 hex characters after 0x. An address fills the last 40, left-padded with zeros, so it starts at character 27; data word k (from zero) starts at character 3 + 64k.

-- Decode ERC-20 Transfer events of one token from raw logs
SELECT block_number, log_index, transaction_hash,
       '0x' || SUBSTR(topic1, 27, 40)  AS from_address,
       '0x' || SUBSTR(topic2, 27, 40)  AS to_address,
       SUBSTR(data, 3, 64)             AS amount_hex    -- first 32-byte word
FROM logs
WHERE contract_address = '0x7a11…0001'
  AND topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL                   -- ERC-721 adds a 4th topic (tokenId)
  AND block_number BETWEEN 21000000 AND 21000999;

In a sample row, data ends in 77359400, which is 2,000,000,000; with the token's 6 decimals that is 2,000 tokens. Converting hex to a number is where engines differ: DuckDB casts to a 128-bit HUGEINT; Trino's from_base(h, 16) and BigQuery's INT64 cast are 64-bit; PostgreSQL needs a user-defined function. The 64-bit limit of about 9.22 × 10^18 raw units is only 9.22 tokens at 18 decimals, so conversions that pass on test data overflow on real transfers. Decode amounts in the ETL step or use a decoded table, store them as decimals, and leave dynamic types (strings, arrays) to an ABI library.

Some values need no conversion. An "unlimited" approval sets the allowance to 2^256 − 1, which is 64 f characters:

-- Unlimited token approvals: allowance = 2^256 - 1 (64 hex 'f')
SELECT block_number, transaction_hash,
       contract_address                AS token,
       '0x' || SUBSTR(topic1, 27, 40)  AS owner,
       '0x' || SUBSTR(topic2, 27, 40)  AS spender
FROM logs
WHERE topic0 = '0x8c5be1e5ebec7d5bd14f71427d1e84f3dd0314c0f7b2291e5b200ac8c7c3b925'
  AND LOWER(data) = '0xffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'
  AND block_number BETWEEN 21000000 AND 21000999;

Lists of owners who granted unlimited approvals to one spender are a standard step in sizing a wallet-drainer campaign or a compromised protocol (Lesson 5.5).

Internal transfers from traces

ETH sent by a contract, such as a pool withdrawal or a multisig payout, does not appear in transactions.value but in traces, one row per call frame. trace_address is the frame's position in the call tree (here text: '' for the top-level call, '0' for its first subcall, '0,1' for the second call inside that; some datasets use arrays). trace_type is call, create, suicide (the historical name for selfdestruct) or reward; call_type is call, delegatecall or staticcall; status and tx_status record whether the frame and the whole transaction succeeded.

-- Internal ETH transfers (sent by contracts) to one address
SELECT block_number, transaction_hash, trace_address,
       from_address, value / 1e18 AS value_eth
FROM traces
WHERE to_address = '0x5e1f…c0de'
  AND value > 0
  AND trace_type = 'call'
  AND call_type = 'call'          -- delegatecall and staticcall move no ETH
  AND trace_address <> ''         -- '' is the top-level call itself
  AND status = 1                  -- this call frame succeeded
  AND tx_status = 1               -- and so did the transaction
  AND block_number BETWEEN 21000000 AND 21050000;

Each filter removes a known false positive. Some trace formats show a value on a delegatecall that was never transferred. Excluding the top-level frame avoids double counting if you also query transactions; to measure all ETH received, use traces alone, including the top-level frame, and remember that create frames with value and selfdestruct frames also move ETH. Status matters more for traces than for logs: a reverted transaction emits no logs, but its traces are still recorded, and a frame can revert inside a successful transaction. Requiring both statuses covers the usual cases, but a revert caught mid-tree can slip through, so check unusual transactions individually.

Gas analytics

Since EIP-1559 (August 2021): fee paid = gas used × effective gas price; burned = gas used × base fee per gas; priority fee = gas used × (effective gas price − base fee). Blob-carrying transactions (EIP-4844) also pay a burned blob fee, blob gas used × blob gas price, recorded in separate receipt fields.

-- Daily fees, burn and priority fees, in ETH
SELECT DATE_TRUNC('day', t.block_timestamp)                         AS day_utc,
       COUNT(*)                                                      AS txs,
       SUM(t.gas_used * t.effective_gas_price) / 1e18                AS fees_eth,
       SUM(t.gas_used * b.base_fee_per_gas) / 1e18                   AS burned_eth,
       SUM(t.gas_used * (t.effective_gas_price - b.base_fee_per_gas)) / 1e18
                                                                     AS tips_eth
FROM transactions AS t                -- failed transactions pay fees too
JOIN blocks AS b ON b.hash = t.block_hash    -- hash, not height: skips replaced blocks
WHERE t.block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND t.block_timestamp <  TIMESTAMP '2024-11-08 00:00:00'
GROUP BY DATE_TRUNC('day', t.block_timestamp)
ORDER BY day_utc;

Unlike value queries, this one deliberately keeps failed transactions, because they consume and pay for gas. Gas times a price in wei easily exceeds 64 bits (10 million gas at 1,000 gwei is 10^19 wei), another reason these columns are decimals. On a small sample, one day shows 0.0239 ETH in fees of which only 0.0030 ETH was burned: a single transaction paying 1,000 gwei per gas (980 gwei above the base fee) dominates, the kind of outlier worth a closer look. Gas data shows who funded an account's gas (a strong link, Lesson 4.3), which senders pay unusually high priority fees (often bots or time-critical attackers) and what an attack cost. On rollups, receipts carry extra L1 data-fee fields whose names differ by chain.

Exam focus: expect to spot the bug in a query: joining on both parts of the outpoint; enrichment and NULL input values; finding UTXOs with an anti-join, with the snapshot condition in ON; aggregating inputs and outputs separately to avoid fan-out; coinbase and BIP 30 duplicates; heuristics as versioned flags and common-input edge lists with CoinJoin exclusions; topic0 as the Keccak-256 of the canonical signature; filtering by contract address against spoofed events; topic3 IS NULL for ERC-20; SUBSTR(topic, 27, 40) and data words at 3 + 64k; zero- versus one-based topic arrays; 64-bit overflow; unlimited approvals; trace filters on call type, trace address and both statuses; EIP-1559 fee, burn and priority formulas, keeping failed transactions in fee totals.