Query the chain yourself: SQL for onchain questions

Why query the chain yourself

The dashboards in reading DeFi data are someone else’s queries with someone else’s definitions of volume, users and flows. Writing your own lets you choose the definitions, see the edge cases, and rerun the numbers whenever you like. A popular public platform for this is Dune, and its engine, DuneSQL, is a fork of the Trino SQL engine with types and functions added for blockchain data. Everything below uses it. Table names are as documented in September 2026; check the data catalog before relying on them, because curated tables are renamed and restructured over time.

Raw, decoded and curated tables

Raw tables mirror the chain. ethereum.logs has the columns you met in reading a transaction: contract_address, topic0 to topic3 and data. Decoded tables apply a contract’s ABI and are named projectname_blockchain.ContractName_evt_EventName for events and ..._call_FunctionName for calls, with every instance of a contract (every Uniswap V3 pool, say) collected in one table. Community members submit contracts for decoding, which usually takes about a day. Curated tables, which Dune calls spells, are built in Spellbook, an open source dbt project, and turn hundreds of protocol specific tables into one schema.

Most of the catalog is free to query on every plan, including all raw and decoded tables, dex.trades, tokens.transfers, prices, labels and the exchange address tables. Some premium datasets are gated, notably the per chain EVM balance tables, sold as an enterprise add-on as of September 2026. That is why the holder query below rebuilds balances from transfers.

DuneSQL essentials

• Addresses and hashes are varbinary. Write them as bare 0x... literals with no quotes, and compare them directly.
• Big numbers have their own types. UINT256 and INT256 hold raw token amounts exactly. A raw amount cannot go negative, so cast to INT256 before subtracting outflows.
• Some column names are reserved words. Quote "from" and "to".
• Byte helpers count bytes, from 1. varbinary_substring(topic1, 13, 20) is the address inside a 32 byte topic; varbinary_to_uint256 turns a word into a number.
• Solana addresses are base58 strings. Dune’s Solana tables store them as varchar, so compare them as quoted strings; use from_base58() only when you need the varbinary form.

Here is the same set of USDC transfers read two ways, first decoded by hand from raw logs, then from the decoded ERC-20 table:

-- USDC transfers decoded by hand from the raw logs table
SELECT
  block_time,
  tx_hash,
  varbinary_substring(topic1, 13, 20) AS sender,       -- last 20 bytes of the topic
  varbinary_substring(topic2, 13, 20) AS recipient,
  varbinary_to_uint256(data) / 1e6    AS amount_usdc   -- USDC has 6 decimals
FROM ethereum.logs
WHERE block_date = DATE '2026-09-27'
  AND contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND topic0 = 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
LIMIT 100

-- The same rows from the decoded table: named columns, raw amounts
SELECT evt_block_time, evt_tx_hash, "from", "to", value / 1e6 AS amount_usdc
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
  AND evt_block_time >= TIMESTAMP '2026-09-27 00:00:00'
  AND evt_block_time <  TIMESTAMP '2026-09-28 00:00:00'
LIMIT 100

Query 1: daily DEX volume for a token

dex.trades holds one row per swap per pool, across dozens of DEXs and chains, with both tokens, display amounts, raw amounts, a USD value, the pool, the transaction sender (tx_from) and the event index. The token you care about can be on either side of a trade, so test both columns. UNI is used here purely as an example.

-- Query 1: daily DEX volume for one token (UNI on Ethereum), last 90 days
SELECT
  block_date,
  SUM(amount_usd)                                     AS volume_usd,
  COUNT(*)                                            AS swaps,
  COUNT(DISTINCT tx_hash)                             AS transactions,
  COUNT(DISTINCT tx_from)                             AS senders,
  SUM(CASE WHEN amount_usd IS NULL THEN 1 ELSE 0 END) AS unpriced_swaps
FROM dex.trades
WHERE blockchain = 'ethereum'
  AND block_month >= date_trunc('month', CURRENT_DATE - INTERVAL '90' DAY)
  AND block_date  >= CURRENT_DATE - INTERVAL '90' DAY
  AND 0x1f9840a85d5af5bf1d1762f925bdaddc4201f984 IN (token_bought_address, token_sold_address)
GROUP BY 1
ORDER BY 1

Three habits are built in. The block_month filter prunes partitions while the block_date filter sets the exact window. Counting distinct transactions and senders beside volume exposes days where a handful of addresses produced most of the activity. And counting unpriced swaps tells you how much volume SUM(amount_usd) silently ignored, because a sum skips nulls without a word. One caution: a route that passes through UNI (X to UNI to Y) appears as two rows that both contain UNI, so this counts it twice; deduplicate on tx_hash if you want trades rather than pool volume.

Query 2: top holders over time

A holder’s balance is the sum of everything received minus everything sent. Doing that per month and carrying each balance forward until it changes gives a snapshot at every month end:

-- Query 2: top 10 UNI holders at the end of each month, rebuilt from transfers
WITH moves AS (
  SELECT "to" AS holder, block_month, CAST(amount_raw AS INT256) AS delta
  FROM tokens.transfers
  WHERE blockchain = 'ethereum'
    AND contract_address = 0x1f9840a85d5af5bf1d1762f925bdaddc4201f984
  UNION ALL
  SELECT "from", block_month, -CAST(amount_raw AS INT256)
  FROM tokens.transfers
  WHERE blockchain = 'ethereum'
    AND contract_address = 0x1f9840a85d5af5bf1d1762f925bdaddc4201f984
),
monthly AS (                  -- one net change per holder per month
  SELECT holder, block_month, SUM(delta) AS net
  FROM moves
  GROUP BY 1, 2
),
running AS (                  -- balance after each active month, and until when it holds
  SELECT holder,
         block_month AS valid_from,
         LEAD(block_month, 1, DATE '9999-12-01')
           OVER (PARTITION BY holder ORDER BY block_month) AS valid_to,
         SUM(net) OVER (PARTITION BY holder ORDER BY block_month) AS balance_raw
  FROM monthly
),
months AS (
  SELECT m FROM UNNEST(SEQUENCE(DATE '2025-01-01', DATE '2026-09-01', INTERVAL '1' MONTH)) AS t(m)
),
ranked AS (
  SELECT m, holder,
         balance_raw / 1e18 AS balance,                 -- UNI has 18 decimals
         ROW_NUMBER() OVER (PARTITION BY m ORDER BY balance_raw DESC) AS rnk
  FROM months
  JOIN running ON m >= valid_from AND m < valid_to
  WHERE holder <> 0x0000000000000000000000000000000000000000
)
SELECT m AS month, rnk, holder, balance
FROM ranked
WHERE rnk <= 10
ORDER BY month, rnk

The running step stores each balance with the month it became valid and the month it stopped being valid, so the join to the calendar fills in months with no activity without generating a row for every holder in every month. The zero address is excluded because mints appear as transfers from it, which would give it a large negative balance.

The result will be dominated by contracts and services: exchanges, bridges, staking and governance contracts, vesting contracts and the protocol’s own treasury. Label them before drawing conclusions, using labels.addresses or cex.addresses, and aggregate labels per address first, because an address can carry several and a naive join multiplies your rows. The guide to supply analysis explains what to do with the answer. For a busy token this query scans the full history; that is the price of correct balances.

Query 3: net flows into an address set

Flows into and out of a group of addresses answer many questions: exchange inflows ahead of selling, a treasury spending its reserves, a cluster from following the money cashing out. Define the set once and classify each transfer by which side of it touches the set:

-- Query 3: daily net flow of UNI into a set of addresses (here, known exchange addresses)
WITH watch AS (
  SELECT DISTINCT address
  FROM cex.addresses
  WHERE blockchain = 'ethereum'
),
t AS (
  SELECT block_date, "from", "to", amount
  FROM tokens.transfers
  WHERE blockchain = 'ethereum'
    AND block_month >= DATE '2026-06-01'
    AND contract_address = 0x1f9840a85d5af5bf1d1762f925bdaddc4201f984
)
SELECT
  t.block_date,
  SUM(CASE WHEN wt.address IS NOT NULL THEN t.amount ELSE 0 END)         AS inflow,
  SUM(CASE WHEN wf.address IS NOT NULL THEN t.amount ELSE 0 END)         AS outflow,
  SUM(CASE WHEN wt.address IS NOT NULL THEN t.amount ELSE -t.amount END) AS net_inflow
FROM t
LEFT JOIN watch wf ON t."from" = wf.address
LEFT JOIN watch wt ON t."to"   = wt.address
WHERE (wf.address IS NULL) <> (wt.address IS NULL)   -- exactly one side is in the set
GROUP BY 1
ORDER BY 1

The condition (wf.address IS NULL) <> (wt.address IS NULL) keeps transfers where exactly one side is in the set, which removes internal shuffles between an exchange’s own wallets that would otherwise inflate both inflow and outflow. To study any other group, replace the watch step with a VALUES list of addresses. Dune also maintains cex.flows, which classifies transfers to and from identified exchange addresses for you, and cex.deposit_addresses, which adds the per customer deposit addresses that forward to exchange hot wallets. Including those catches deposits before they are consolidated.

The pitfalls behind confident wrong numbers

Wash trading deserves its own warning. In October 2024 the SEC charged three firms it called so-called market makers with generating artificial volume for token promoters, and raw DEX volume includes that kind of activity by construction. Volume per distinct sender, and volume from wallets with no history before the trading began, are cheap first filters.

Chains differ too. On Solana, tokens sit in token accounts owned by wallets, so map token accounts to their owners before counting holders, and use the Solana tables (dex_solana.trades, tokens_solana.transfers, solana.transactions) rather than forcing EVM habits onto them. On rollups, fees include a charge for posting data to Ethereum, block times are far shorter, and the same address on two chains can be two different contracts. Curated tables also lag the chain: Dune documents refreshes of about 30 minutes for prices and about an hour for exchange flows.

Beyond Dune

The concepts carry over unchanged. Raw logs and traces, ABI decoding, curated models and the pitfalls above are the same everywhere; only the table names change.

A query workflow that holds up

1. Write the question as a sentence

   "Daily net UNI inflow to exchanges over 90 days" can be queried. "Are whales selling?" cannot, until you define whale, selling and the time window.

2. Pick the most curated table that answers it

   Drop to decoded or raw tables only when the curated one does not cover your contract or hides a detail you need.

3. Prototype on one day and one known transaction

   A narrow partition filter keeps iterations fast, and a transaction you have decoded by hand tells you whether the columns mean what you think.

4. Scale up, then sanity check

   Widen the date range, then look for gaps, spikes, unpriced rows and the largest individual contributors.

5. Publish with the caveats attached

   State the table versions, the filters and the known blind spots. Anyone should be able to rerun your number.

The documentation for dex.trades and tokens.transfers lists every column and its meaning, and is worth reading once before you build anything on either.

Common questions

Where to go next