Skip to main content

​
Overview

This cookbook provides ready-to-use query patterns for common DeFi analytics tasks. All examples use BlockDB’s canonical datasets and can be adapted to your specific use cases.
All queries use the _tracing_id field for lineage tracking. Use this field to verify data provenance and join across datasets.

​
Common Patterns

​
Token Price History

Retrieve OHLC price data for a token pair: 
SELECT 
  bucket,
  open,
  high,
  low,
  close,
  volume_base,
  volume_quote
FROM blockdb0404_token_to_token_prices_ohlc_v1
WHERE chain_id = 1
  AND base_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH
  AND quote_code = 'USD'
  AND bucket >= '2025-01-01T00:00:00Z'
ORDER BY bucket DESC
LIMIT 100;

​
Pool Reserve Snapshots

Track liquidity pool reserves over time: 
SELECT 
  pool_uid,
  block_number,
  block_time,
  reserves[1] as reserve0,
  reserves[2] as reserve1,
  current_tick,
  _tracing_id
FROM blockdb0301_liquidity_pools_reserves_v1
WHERE chain_id = 1
  AND pool_uid = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640000000000000000000000000'
  AND block_time >= '2025-01-01T00:00:00Z'
ORDER BY block_number ASC;

​
Transaction Volume by Token

Calculate total transaction volume for a specific token: 
SELECT 
  t.to_address,
  SUM(CAST(t.effective_gas_price_wei AS NUMERIC) * t.gas_used) / 1e18 as total_eth_volume,
  COUNT(*) as tx_count
FROM blockdb0102_transactions_v1 t
WHERE t.chain_id = 1
  AND t.to_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC
  AND t.block_time >= '2025-01-01T00:00:00Z'
  AND t.status_success = true
GROUP BY t.to_address;

​
Event Log Analysis

Find all Transfer events for a specific token: 
SELECT 
  l.block_number,
  l.block_time,
  l.tx_hash,
  l.contract_address,
  l.topic_zero,
  l.data_topics,
  l._tracing_id
FROM blockdb0103_logs_v1 l
WHERE l.chain_id = 1
  AND l.contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC
  AND l.topic_zero = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer(address,address,uint256)
  AND l.block_time >= '2025-01-01T00:00:00Z'
ORDER BY l.block_number DESC
LIMIT 100;

​
VWAP Calculation

Get volume-weighted average price for a token pair: 
SELECT 
  bucket,
  vwap,
  volume_base,
  volume_quote,
  _tracing_id
FROM blockdb0501_token_to_token_vwap_v1
WHERE chain_id = 1
  AND base_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH
  AND quote_code = 'USD'
  AND bucket >= '2025-01-01T00:00:00Z'
ORDER BY bucket DESC;

​
Advanced Patterns

​
Cross-Dataset Joins

Join transactions with logs to analyze contract interactions: 
SELECT 
  t.tx_hash,
  t.from_address,
  t.to_address,
  t.gas_used,
  l.contract_address,
  l.topic_zero,
  l.log_index
FROM blockdb0102_transactions_v1 t
INNER JOIN blockdb0103_logs_v1 l
  ON t.chain_id = l.chain_id
  AND t.block_number = l.block_number
  AND t.tx_hash = l.tx_hash
WHERE t.chain_id = 1
  AND t.block_time >= '2025-01-01T00:00:00Z'
  AND l.contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
ORDER BY t.block_number DESC, l.log_index ASC;

​
Lineage Tracing

Trace data lineage using _tracing_id: 
SELECT 
  b._tracing_id as block_tracing_id,
  t._tracing_id as tx_tracing_id,
  l._tracing_id as log_tracing_id,
  b.block_number,
  t.tx_hash,
  l.contract_address
FROM blockdb0101_blocks_v1 b
INNER JOIN blockdb0102_transactions_v1 t
  ON b.chain_id = t.chain_id AND b.block_number = t.block_number
INNER JOIN blockdb0103_logs_v1 l
  ON t.chain_id = l.chain_id AND t.tx_hash = l.tx_hash
WHERE b.chain_id = 1
  AND b.block_number = 12345678
ORDER BY l.log_index;

​
Best Practices

Always filter by chain_id first for optimal query performance. BlockDB indexes are optimized for chain-based queries.
When querying large time ranges, use pagination with cursor tokens. Avoid fetching more than 10,000 records in a single request.
Use _tracing_id to maintain referential integrity when joining across datasets. This field provides cryptographic proof of data lineage.

​
See Also