SQL foundations for blockchain tables

SQL is the common language of blockchain analytics: public datasets, hosted platforms and in-house warehouses all answer to it. A handful of clauses covers most questions an analyst asks, but blockchain tables add their own traps, from 18-decimal integers to failed transactions that still sit in the data. This lesson sets out the generic schema used throughout the module and builds the first worked queries on it.

A generic schema for blockchain tables

Every dataset names its tables and columns a little differently, so this module uses a neutral generic schema modeled on the open-source ETL tools behind the main public datasets. Translating to a real dataset is usually a matter of renaming.

Conventions: addresses and hashes are lowercase hex strings with the 0x prefix; amounts (value, amount_raw, gas prices) are raw integers in base units stored as DECIMAL(38,0); timestamps are UTC; status is 1 for success and 0 for failure. In transactions, gas_used and effective_gas_price come from the receipt. Bitcoin-style inputs and outputs tables are introduced in the lesson Querying UTXO data.

Real datasets differ in details you must check. The BigQuery Ethereum dataset, for instance, prefixes receipt fields (receipt_status, receipt_gas_used), calls the log's contract address, and stores topics as an array. Some datasets store large integers as strings.

SELECT, WHERE and ORDER BY

The first question in most cases is simple: what came into this address?

-- Successful ETH transfers sent to one address in a block range
SELECT block_number,
       block_timestamp,
       hash,
       from_address,
       value / 1e18 AS value_eth                -- wei to ETH, for display
FROM transactions
WHERE to_address = '0x5e1f…c0de'               -- lowercase, as stored
  AND block_number BETWEEN 21000000 AND 21050000
  AND status = 1                                -- drop failed transactions
  AND value > 0
ORDER BY block_number, transaction_index;

SELECT names the columns returned, FROM the table, WHERE the filters, ORDER BY the sort. Each filter matters:

• Case. Addresses are compared as strings. Checksummed addresses mix upper and lower case, so normalize the literal with LOWER() or type it in lowercase.
• Scope. A block range limits the scan and makes the query reproducible: the same range returns the same rows next year.
• Status. Failed transactions are recorded on-chain and paid fees, but their value transfer was reverted. Forgetting status = 1 is the most common way to overstate inflows.
• Value. Many transactions to an address carry zero ETH because they are contract calls.

Mind the NULLs. A transaction that deploys a contract has no recipient, so its to_address is NULL. Comparisons with NULL are neither true nor false, which means a filter such as to_address <> '0x5e1f…c0de' silently drops every contract creation; write to_address IS NULL OR to_address <> … when you mean "everything else". Likewise COUNT(to_address) skips NULLs while COUNT(*) does not, so the two can differ on the same rows.

Sort by block_number, transaction_index to get chain order. Sorting by timestamp is not enough: every transaction in a block shares the same timestamp. Note also that this query sees only top-level ETH transfers; ETH sent by contracts appears in traces, covered in the lesson Querying EVM logs and traces.

Units and numeric types

On-chain amounts are integers in the smallest unit:

These integers are large. A signed 64-bit integer stops at about 9.22 × 10^18, which is only 9.22 ETH in wei. Store and sum amounts as DECIMAL(38,0) or NUMERIC (or the dataset's big-number type), sum the raw integers first, and divide once at the end. Dividing by 1e18 produces a floating-point number: fine for display, not for reconciliation, where 0.1 + 0.2 must equal 0.3 exactly.

Watch integer division. In PostgreSQL, Trino and SQLite, dividing one integer by another truncates (5 / 2 = 2); in BigQuery and DuckDB / returns a fractional result. Multiply by 1.0 or cast when computing ratios.

Time bucketing

Analysts constantly ask "per day" or "per hour". Truncate the timestamp to the bucket and group by it.

-- Daily inflow to the address, bucketed by UTC day
SELECT DATE_TRUNC('day', block_timestamp)  AS day_utc,
       COUNT(*)                            AS tx_count,
       COUNT(DISTINCT from_address)        AS senders,
       SUM(value) / 1e18                   AS eth_received
FROM transactions
WHERE to_address = '0x5e1f…c0de'
  AND status = 1
  AND block_timestamp >= TIMESTAMP '2024-11-01 00:00:00'
  AND block_timestamp <  TIMESTAMP '2024-12-01 00:00:00'
GROUP BY DATE_TRUNC('day', block_timestamp)
ORDER BY day_utc;

On a small sample this returns:

The sample also holds a failed 2 ETH transfer on November 1; without status = 1 that day would show 4.25 ETH. The time filter is a half-open interval (>= start, < end), which never double-counts a row that falls exactly on midnight.

The truncation function is one of the most common dialect differences:

Keep everything in UTC and convert only for presentation, stating the time zone in the report. Remember that block timestamps are set by the block producer, have a resolution of one second and, on Bitcoin, can differ from real time by more than an hour (see the lesson Blocks and headers as data).

Aggregation with GROUP BY

GROUP BY collapses rows into one row per group, and aggregate functions summarize each group: COUNT(*) counts rows, COUNT(DISTINCT x) counts unique values, SUM, MIN, MAX and AVG do what their names say. HAVING filters groups after aggregation, as WHERE filters rows before it. Token amounts need one more step: a join to the token's decimals.

-- Token transfers out of the address, converted with each token's decimals
SELECT tt.token_address,
       t.symbol,
       COUNT(*)                                     AS transfers,
       SUM(tt.amount_raw) / POWER(10, t.decimals)   AS amount
FROM token_transfers AS tt
JOIN tokens AS t
  ON t.token_address = tt.token_address
WHERE tt.from_address = '0x5e1f…c0de'
  AND tt.block_number BETWEEN 21000000 AND 21050000
GROUP BY tt.token_address, t.symbol, t.decimals   -- never group by symbol alone
ORDER BY transfers DESC;

The query sums raw amounts per token contract, then divides by 10 to the power of that token's decimals. The grouping key is the token address, not the symbol. Anyone can deploy a contract called "USDC"; in the sample data an imitation token with 18 decimals sits next to the real one with 6, and grouping by symbol would add 1,000 worthless tokens to 2,000 genuine ones and divide the mix by a single, wrong decimal count. JOIN here is an inner join, so transfers of tokens missing from tokens disappear silently; the next lesson shows how to keep them with a LEFT JOIN.

Some engines offer approximate functions such as APPROX_COUNT_DISTINCT that are much cheaper on billions of rows. They are fine for dashboards but should not supply figures in a report unless you say so.

A checklist of common mistakes

Exam focus: the grain and key columns of blocks, transactions, logs, traces and token_transfers; why failed transactions must be excluded when measuring value moved; base units (wei 10^18, gwei 10^9, satoshi 10^8, token decimals) and why 64-bit integers overflow; summing raw integers before converting; time bucketing with half-open UTC intervals and the main dialect differences for truncating dates; chain order by block number and transaction index; grouping tokens by contract address, never by symbol.