Skip to content

SQL Query Guide

Query the full chain data store using ClickHouse SQL — every block, transaction, transfer, swap, log, and trace.

Overview

ChainGraph stores all indexed blockchain data in ClickHouse, a columnar analytics database optimized for fast aggregations over billions of rows. Access it through the Power Mode query editor inside any investigation.

Read-only. Only SELECT, WITH, SHOW, DESCRIBE, and EXPLAIN statements are allowed. All queries execute with readonly=1 and a 30-second timeout.

Tables

blocks

Every Ethereum block header.

chain_id UInt32, block_number UInt64, block_hash String,
parent_hash String, timestamp DateTime, miner String,
gas_used UInt64, gas_limit UInt64, base_fee Nullable(UInt64),
tx_count UInt32

transactions

Every transaction receipt.

chain_id UInt32, block_number UInt64, block_timestamp DateTime,
tx_hash String, tx_index UInt32, from_address String,
to_address Nullable(String), value_wei UInt256,
gas_price_wei UInt64, gas_used UInt64, status UInt8,
tx_type UInt8, method_selector Nullable(String)

native_transfers

Native ETH value transfers (including internal calls).

chain_id UInt32, block_number UInt64, block_timestamp DateTime,
tx_hash String, from_address String, to_address String,
value_wei UInt256, transfer_type String

erc20_transfers

ERC-20 token Transfer events.

chain_id UInt32, block_number UInt64, block_timestamp DateTime,
tx_hash String, token_address String, from_address String,
to_address String, amount_raw UInt256, decimals UInt8,
amount Float64, token_symbol Nullable(String)

dex_swaps

DEX swap events (Uniswap V2/V3, SushiSwap, etc.).

chain_id UInt32, block_number UInt64, block_timestamp DateTime,
tx_hash String, protocol String, pool_address String,
sender_address String, recipient_address String,
token_in_address String, token_in_symbol String, token_in_amount UInt256,
token_out_address String, token_out_symbol String, token_out_amount UInt256,
price_ratio Nullable(Float64)

Other tables

Also available: logs, traces, contract_creations,address_metadata, token_metadata, price_feeds,mev_transactions, staking_events, bridge_transfers,nft_transfers.

Use DESCRIBE table_name to see the full schema of any table.

Example queries

Recent blocks

SELECT block_number, block_hash, miner, gas_used, tx_count, timestamp
FROM blocks
WHERE chain_id = 1
ORDER BY block_number DESC
LIMIT 25

Top ERC-20 senders by transfer count

SELECT from_address, count() AS transfer_count,
       uniqExact(token_address) AS unique_tokens
FROM erc20_transfers
WHERE chain_id = 1
GROUP BY from_address
ORDER BY transfer_count DESC
LIMIT 25

DEX swap volume by protocol

SELECT protocol, count() AS swaps,
       uniqExact(sender_address) AS unique_traders
FROM dex_swaps
WHERE chain_id = 1
GROUP BY protocol
ORDER BY swaps DESC

Largest native transfers

SELECT tx_hash, from_address, to_address, value_wei, block_number
FROM native_transfers
WHERE chain_id = 1
ORDER BY value_wei DESC
LIMIT 25

Table sizes

SELECT table, formatReadableSize(sum(bytes)) AS size,
       sum(rows) AS total_rows
FROM system.parts
WHERE database = 'chaingraph' AND active
GROUP BY table
ORDER BY sum(bytes) DESC

ClickHouse functions

Useful ClickHouse-specific functions:

  • toDateTime(timestamp) — convert to datetime
  • formatReadableSize(bytes) — human-readable byte sizes
  • uniqExact(column) — exact distinct count
  • quantile(0.95)(column) — percentile
  • arrayJoin(array) — unnest arrays
  • dictGet('dict', 'attr', key) — dictionary lookup

Rate limits

SQL queries share the same Power Mode rate limits as Cypher queries: 10/day on Free tier, 200/day on Pro. All queries are capped at 10,000 result rows and 30 seconds execution time.

SQL Query Guide — ChainGraph Docs