How to query a blockchain with SQL

The short way

Use Dune. Sign in, open the query editor, and run SQL against a table somebody else already built and keeps running. Nothing is installed, no node is involved, and the first query is a browser tab.

SELECT date_trunc('day', block_time) AS day,
       count(*)                      AS trades
FROM dex.trades
WHERE blockchain = 'ethereum'
  AND block_time >= TIMESTAMP '2026-09-01'
  AND block_time <  TIMESTAMP '2026-09-08'
GROUP BY 1
ORDER BY 1

Both filters in that WHERE clause are load-bearing, and not for the reason they look it. Large cross-chain tables here are partitioned by blockchain as well as by time, and Dune's own efficiency guidance is to "specify the blockchain filter along with time filters to dramatically reduce the amount of data scanned". Data scanned is what you are billed for, so the filter you left off is a line on the invoice rather than a slower answer.

Once the query works, the same thing runs over HTTP on every plan, the free one included — a saved query is executed, polled and read back:

POST /v1/query/{query_id}/execute
GET  /v1/execution/{execution_id}/status
GET  /v1/execution/{execution_id}/results
X-Dune-Api-Key: YOUR_API_KEY

That is the whole mechanism. The rest of this page is what the tables underneath it are, and what they are not.

What the options are

Free SQL with the API attached. Dune is the default because of an unusual pricing decision rather than a technical one: API access is on every plan, including Free, where the same programmatic access is normally a separate and dearer product than the editor. The catalogue is 100+ chains in three layers — raw (transactions, logs, traces, blocks), ABI-decoded per-contract event and call tables, and curated cross-protocol tables such as dex.trades and dex_aggregator.trades. Depth is uneven on purpose: Solana has its own shape, with instruction_calls, account_activity and dex_solana.trades, and Bitcoin has raw blocks, inputs, outputs and transactions with no decoded layer at all, because there is nothing to decode.

The on-ramp for people who do not write SQL yet. Footprint Analytics is a Metabase fork, which is the reason its two halves fit: in Metabase a question built by clicking is an abstraction over SQL, so a chart can be opened as a query and kept going. SQL and Python notebooks are both on the free plan, and the paid tier buys capacity and privacy rather than features — 30 GB per query instead of 1 GB, private dashboards, no watermark. Read that card before building on it: coverage is 31 named chains with traces on six of them, the vendor's own pages give three different chain counts, and nothing on the free plan can be kept private.

The institutional version of the same job. Allium is Snowflake-backed SQL over 85+ indexed chains with a curated label set covering 400M+ addresses — exchange hot, cold and deposit wallets, sanctions lists, country attribution. That label layer is the difference between asking what happened and asking who did it, and it is the expensive half of this category to build. There is no published price and no free tier, so this is a sales cycle before it is a query.

The same purchase without SQL. Bitquery sells the indexing and the decoding behind a typed GraphQL schema with WebSocket subscriptions over it. The trade is explicit: a SQL warehouse gives you joins, window functions and a query that grinds over two years of history; a typed schema answers fast over a rolling window and streams the same query live. If the question repeats every few seconds on one pair, that is the better shape. If the question is "what happened across 2024", it is the wrong one — see the retention note below.

Where this breaks

A decoded table exists because somebody wrote the decoder. This is the first thing that surprises people who expect SQL over "the chain". Decoded tables are built from contract ABIs, and Dune's documentation says plainly that "contract submissions on Dune are driven by the community" and that once submitted, "decoding usually takes about 24 hours, in special cases it might take longer". So a protocol that launched this morning is not a missing row in an existing table, it is a table that does not exist. The raw logs are there and you can decode topics and data yourself in SQL, which is exactly the work the decoded layer was saving you. The same asymmetry runs through curated tables: dex.trades spans protocols because somebody maintains the mapping from each one's events into a common shape, and a new DEX joins that shape when the mapping is written.

The meter is compute, not rows. Credits "reflect the actual compute resources a query uses, including processing power, data scanned, and the time it occupies the query engine", and the documentation is direct that "you'll see costs after execution" — there is no estimate first. That combination is what makes exploratory work expensive in a way a row count does not predict: a query returning nine rows can scan a year of a partitioned table to find them. The two defences are both ones you have to set yourself, a per-execution cost cap and a monthly extra-credit limit, and neither is a preview. Allium meters the same way from the other end — Explorer "query cost scales with the compute profile and how long the query runs", the units are "charged when the run was queued", and running SQL and then saving the identical SQL "bills the compute twice".

The engine gives up before a full-history scan does. Dune's Small engine "will time out after 2 minutes" and limits you to "a maximum of three (3) concurrent executions". A two-minute ceiling is not a small version of a big engine; it is a different class of question. Anything that walks all of a chain's transfers, or joins two large tables without a partition filter, does not run slowly on it — it does not run. The workaround that survives is structural rather than clever: filter on the partition columns, materialise an intermediate result, and build the long answer out of narrow ones.

"Latest" is behind the chain, deliberately, and by a different amount per chain. Reorg-prone chains get "a deliberate delay in data ingestion", and raw update frequency follows "the blockchain's frequency of producing blocks and its likelihood of a reorg". On top of that, curated tables "are usually scheduled to run once every hour", with Solana sets such as dex_solana.trades and tokens_solana.transfers on a six-hour schedule. So three different numbers stand between your now() and the chain tip: settlement, ingestion lag and transformation cadence. A daily total that includes today is a partial day on every one of these platforms, and none of them will mark it as one. Where an on-chain number comes from is the full version of that stack, including why two vendors disagree about the same metric.

History is a separate purchase, and on some plans it is not there at all. Bitquery's self-service plans are a real-time window: trades, prices and OHLC keep 30 days, and raw on-chain data keeps four hours on EVM chains and eight on Solana. Its archive database — "all blocks from the genesis (first one)" — is a per-chain add-on, and a query that reaches into it without one is "rejected" with an error naming the add-on you need. The archive is also not the live dataset: it carries "the delay from tens of minutes to several hours, depending on the blockchain". Footprint's free plan caps a query at 1 GB of data rather than at a date, which is the same limit wearing different clothes. The general rule for this category is that the deep, joinable, decoded history is what the plan price is actually for, and the free tier is a window onto it.

If you outgrow this

If the problem is publishing what you computed, check the licence before the price. Dune's own plan comparison marks data licensing and redistribution as internal use only on Free, Analyst and Plus alike, with a commercial licence reserved for Enterprise; Bitquery permits open publication of data obtained through the service solely to paid users and forbids reselling or sub-licensing without written consent. Neither of those is a tier you can buy your way past on a pricing page. Redistribution is what that word means here, and it is the clause that decides whether a dashboard can face a customer.

If the problem is who rather than what, you have hit the ceiling of a query engine. No amount of SQL turns a hex address into a named venue; that is an attribution layer, and Allium sells one while the self-serve end of on-chain analytics largely does not. Bitquery sells labels as a separate monthly add-on. Budget for the label set as its own line, not as a feature of the query tool.

If the problem is that the query runs every few seconds, stop querying. A warehouse answers a question you asked; a subscription tells you when something changed. That is Bitquery's shape, and if the question is narrower still — one pool, one pair, right now — it is not a query at all, and getting a DEX pair price is the cheaper page.

If the problem is that a chain you need is thin, check the specific tables rather than the headline. Chain counts describe breadth and say nothing about depth: traces exist on six of Footprint's chains, Bitcoin carries no decoded layer anywhere, and Solana's tables are a different shape from the EVM ones rather than the same shape with another chain name in a column. The Solana shelf is where that difference is most visible.

And if the problem is that this could all be somebody else's platform to close, it is a real one and it has a recent precedent: what to use instead of Flipside is the version of this page for people who already had their queries written. Nothing here reproduces another platform's table names, column layouts or SQL dialect, so portability is a rewrite, not a connection string. The part that does travel is the definition you wrote down — which is the argument for keeping the query, in a repository, next to a sentence explaining what it counts.