The analytics stack and SQL for blockchain tables

Almost every number in an on-chain report has passed through a pipeline between the node that saw the block and the query that produced the figure. An investigator who knows which layer a table belongs to, who built it, how fresh it is and what rules shaped it can tell a real finding from a pipeline artifact. SQL is the common language at the top of that stack: public datasets, hosted platforms and in-house warehouses all answer to it. A handful of clauses covers most questions, but blockchain tables add their own traps, from 18-decimal integers to failed transactions that still sit in the data. This lesson maps the stack, sets out the generic schema used in the rest of this module and builds the first worked queries.

From node to table

A node serves blocks, transactions, receipts and state through RPC calls (Lesson 2.1). Analysts need rows and columns. The process that turns one into the other is an ETL pipeline: extract, transform, load.

• Extract. Call the node for every block in a range: the block with its transactions, the receipts with their logs and, where supported, the call traces.
• Transform. Flatten nested JSON into one row per record, convert hex strings to numbers, copy the block timestamp onto every transaction and log so queries need no join for time, and normalize identifiers (for example, lowercase addresses).
• Load. Write rows to storage, usually columnar Apache Parquet files partitioned by date or block range, or database tables. Open table formats such as Apache Iceberg and Delta Lake add versioned snapshots on top.

The key idea is grain: what one row represents. One Ethereum block with 150 transactions and 400 event logs becomes one row in blocks, 150 in transactions and 400 in logs. Most query errors come from forgetting a table's grain and counting or summing at the wrong level.

Pipelines run as a one-off backfill of history or as streaming (incremental) loads that follow the chain head. Near the head, blocks can be replaced by a reorganization, so a pipeline either waits for a confirmation depth or a finalized block, or rewrites recent blocks when the chain changes. Open-source extractors include the blockchain-etl project's Ethereum ETL and Bitcoin ETL and cryo, which writes EVM data straight to Parquet. They are worth knowing even if you never run them: many public datasets were built with them and inherit their column names.

Raw, decoded and curated tables

Each step up shortens queries and adds decisions someone else made. Does a curated token_transfers table include ERC-721 transfers or only fungible tokens? Are mints and burns rows with the zero address, or excluded? Is the amount raw or already divided by decimals? Two datasets that both call a table token_transfers can disagree on all three. The protective habit is reconciliation: check the curated table against the raw layer on a small block range.

-- Does the curated token_transfers table match the raw logs it came from?
SELECT 'raw logs' AS layer, COUNT(*) AS n
FROM logs
WHERE topic0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
  AND topic3 IS NULL              -- fungible (ERC-20 style) transfers only
  AND block_number BETWEEN 21000000 AND 21000099
UNION ALL
SELECT 'token_transfers', COUNT(*)
FROM token_transfers
WHERE block_number BETWEEN 21000000 AND 21000099;

The first half counts raw Transfer events (the hex string is the event's signature hash, explained in Lesson 2.9); the second counts curated rows for the same 100 blocks. If they differ, find out why before using the table: it may drop some tokens, include NFTs or miss blocks.

A generic schema for blockchain tables

Datasets name tables and columns differently, so this module uses a neutral generic schema modeled on the open-source ETL tools. Translating to a real dataset is usually 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; gas_used and effective_gas_price in transactions come from the receipt; block_hash lets queries join transactions to blocks by hash rather than height, which drops rows left over from blocks replaced in a reorganization (Lesson 2.1). labels.grade holds the A–D grade of the link between the address and the entity, A strongest (Lessons 3.2 and 4.5), so sorting by grade ascending puts the strongest labels first. The examples query one chain at a time; tables that hold several chains add a chain_id column to every row and to every key and join (Lesson 2.6). UTXO inputs and outputs tables are introduced in Lesson 2.9. Real datasets differ in details you must check: the BigQuery Ethereum dataset prefixes receipt fields (receipt_status, receipt_gas_used), calls the log's contract address and stores topics as an array, and 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;

Each filter matters:

• Case. Addresses compare as strings, and checksummed addresses mix case: 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 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 contract-creation transaction has no recipient, so its to_address is NULL, and comparisons with NULL are neither true nor false: 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 and COUNT(*) does not.

Sort by block_number, transaction_index for chain order; every transaction in a block shares one timestamp. This query sees only top-level ETH transfers; ETH sent by contracts appears in traces (Lesson 2.9).

Units and numeric types

A signed 64-bit integer stops at about 9.22 × 10^18, only 9.22 ETH in wei. Store and sum amounts as DECIMAL(38,0) or NUMERIC, sum raw integers first and divide once at the end. DECIMAL(38,0) holds values up to about 10^38; the few that exceed it, such as an unlimited approval of 2^256 − 1, need an unbounded NUMERIC, a 256-bit type or a hex string. Dividing by 1e18 yields a floating-point number: fine for display, not for reconciliation. Watch integer division too: in PostgreSQL, Trino and SQLite 5 / 2 = 2, while BigQuery and DuckDB return 2.5; multiply by 1.0 or cast when computing ratios.

Time bucketing

-- 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;

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 exactly at midnight. The truncation function is a common dialect difference: DATE_TRUNC('day', ts) in PostgreSQL, DuckDB, Trino, Snowflake and Spark SQL; TIMESTAMP_TRUNC(ts, DAY) or DATE(ts) in BigQuery; DATE(ts) in SQLite; toStartOfDay(ts) in ClickHouse. Bucket in UTC, convert only for presentation, and remember that block timestamps have one-second resolution and, on Bitcoin, can be off by more than an hour (Lesson 2.1).

Aggregation with GROUP BY

GROUP BY collapses rows into one per group; COUNT(*), COUNT(DISTINCT x), SUM, MIN, MAX and AVG summarize each group; HAVING filters groups after aggregation as WHERE filters rows before it. Token amounts need a join to each 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 grouping key is the token address, not the symbol. Anyone can deploy a contract called "USDC": in the sample data an imitation with 18 decimals sits next to the real token with 6, and grouping by symbol would add 1,000 worthless tokens to 2,000 genuine ones and divide the mix by one wrong decimal count. JOIN is an inner join, so transfers of tokens missing from tokens vanish silently; Lesson 2.8 shows how to keep them with a LEFT JOIN. Approximate functions such as APPROX_COUNT_DISTINCT are fine for dashboards but should not supply report figures unless you say so.

Public datasets, engines and notebooks

• Google BigQuery public datasets. crypto_bitcoin, crypto_ethereum and others, built with the blockchain-etl tools; billed by data scanned beyond a free allowance.
• AWS Public Blockchain Data. Compressed Parquet files in a public bucket, partitioned by date and updated daily; readable by any Parquet-capable engine.
• Hosted query platforms. Community platforms with raw, decoded and curated tables and a free tier; their curated tables follow each provider's rules.

Public does not mean correct or current: datasets lag by minutes to a day, change schemas and occasionally miss blocks. Profile a table before trusting it:

-- Coverage, freshness and duplicates
SELECT MIN(block_number)     AS first_block,
       MAX(block_number)     AS last_block,
       MAX(block_timestamp)  AS latest_data_utc,
       COUNT(*)              AS row_count,
       COUNT(DISTINCT hash)  AS distinct_hashes  -- should equal row_count
FROM transactions;

On a pay-per-scan engine, run it on a recent date range first (Lesson 3.6).

Core SQL is shared, but function names, date handling, arrays and table naming differ. These three queries all count the blocks of one UTC day:

-- Public dataset in BigQuery (project.dataset.table):
SELECT COUNT(*) FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE DATE(timestamp) = '2024-11-01';
-- DuckDB reading Parquet files directly:
SELECT COUNT(*) FROM read_parquet('eth/blocks/date=2024-11-01/*.parquet');
-- Any engine holding the generic schema:
SELECT COUNT(*) FROM blocks WHERE CAST(block_timestamp AS DATE) = DATE '2024-11-01';

Notebooks (Jupyter and similar) combine SQL, Python or R, charts and written reasoning in one document, which suits investigations. Iterative graph work such as connected components for clustering fits SQL poorly; analysts export edges to a graph library (Lesson 2.9).

Choosing tools and avoiding common mistakes

Weigh coverage (chains, date range, traces, decoded events), freshness, cost model, reproducibility (can you pin a snapshot or block height?), provenance (can each row be traced to a block hash and transaction hash?) and confidentiality. Hosted services log queries, and some community platforms make them public by default: typing a suspect's address into a shared platform can reveal an investigation. For confidential work, use your own node or downloaded files and a local embedded engine. A journalist checking one wallet can use a public dataset and export the rows used; an exchange screening deposits needs a streaming pipeline into a columnar database; a study that must be reproduced exactly needs a pinned Parquet snapshot and versioned code. Whatever you choose, record the dataset, its version or snapshot date, the query text and the run time with every result.

Exam focus: expect questions that hand you a query or a dataset and ask what is wrong with the result: the ETL stages and what transform does; grain and why one block becomes many rows; raw, decoded and curated layers and the need to reconcile curated tables; the main public datasets and their limits; base units and 64-bit overflow; excluding failed transactions from value but not from fees; NULL to_address on contract creations; half-open UTC intervals and dialect differences in date truncation; chain order by block number and transaction index; grouping tokens by contract address; confidentiality of hosted queries and recording provenance.