Query Examples

SELECT 
  blockchain,
  SUM(amount_usd) as volume_usd,
  COUNT(DISTINCT tx_hash) as transaction_count,
  COUNT(DISTINCT trader) as unique_traders
FROM dex.trades
WHERE block_time >= NOW() - INTERVAL '7' day
GROUP BY blockchain
ORDER BY volume_usd DESC

SELECT 
  DATE_TRUNC('day', block_time) as date,
  blockchain,
  COUNT(DISTINCT tx_hash) as daily_transactions
FROM transactions
WHERE blockchain IN ('arbitrum', 'optimism', 'base', 'zksync', 'linea', 'scroll')
  AND block_time >= NOW() - INTERVAL '30' day
GROUP BY 1, 2
ORDER BY date, blockchain

SELECT 
  project as marketplace,
  SUM(amount_usd) as volume_usd,
  COUNT(*) as total_trades,
  AVG(amount_usd) as avg_sale_price,
  COUNT(DISTINCT seller) as unique_sellers,
  COUNT(DISTINCT buyer) as unique_buyers
FROM nft.trades
WHERE blockchain = 'ethereum'
  AND block_time >= NOW() - INTERVAL '30' day
GROUP BY marketplace
ORDER BY volume_usd DESC
LIMIT 10

SELECT 
  project as dex_protocol,
  blockchain,
  SUM(amount_usd) as volume_usd,
  COUNT(*) as swap_count,
  COUNT(DISTINCT trader) as unique_traders
FROM dex.trades
WHERE block_time >= CURRENT_DATE - INTERVAL '7' day
  AND project IN ('uniswap', '1inch')
GROUP BY project, blockchain
ORDER BY volume_usd DESC

SELECT 
  symbol as stablecoin,
  SUM(amount) as total_supply,
  COUNT(DISTINCT holder) as unique_holders
FROM tokens.balances
WHERE symbol IN ('USDT', 'USDC', 'DAI', 'USDS', 'USDe', 'crvUSD')
  AND blockchain = 'ethereum'
  AND date = CURRENT_DATE
GROUP BY symbol
ORDER BY total_supply DESC

SELECT 
  symbol,
  DATE_TRUNC('day', block_time) as date,
  SUM(amount_usd) as transfer_volume_usd,
  COUNT(*) as transfer_count
FROM tokens.transfers
WHERE symbol IN ('usdt', 'usdc', 'dai', 'usds')
  AND blockchain = 'ethereum'
  AND block_time >= NOW() - INTERVAL '90' day
GROUP BY symbol, date
ORDER BY date, symbol

SELECT 
  CASE 
    WHEN version = '2' THEN 'evm_<color:uniswap> v2'
    WHEN version = '3' THEN 'evm_<color:uniswap> v3'
    WHEN version = '4' THEN 'evm_<color:uniswap> v4'
  END as protocol_version,
  SUM(amount_usd) as volume_usd,
  COUNT(*) as trade_count
FROM dex.trades
WHERE project = 'uniswap'
  AND block_time >= NOW() - INTERVAL '7' day
GROUP BY version
ORDER BY volume_usd DESC

SELECT 
  CONCAT('evm_<color:', blockchain, '> - Uniswap') as chain_protocol,
  SUM(amount_usd) as volume_usd,
  COUNT(DISTINCT trader) as unique_traders
FROM dex.trades
WHERE project = 'uniswap'
  AND block_time >= NOW() - INTERVAL '30' day
GROUP BY blockchain
ORDER BY volume_usd DESC

SELECT 
  provider,
  SUM(amount_eth) as total_staked_eth,
  SUM(amount_usd) as total_staked_usd,
  COUNT(DISTINCT staker) as unique_stakers,
  AVG(amount_eth) as avg_stake_size
FROM ethereum.staking_deposits
WHERE block_time >= NOW() - INTERVAL '30' day
  AND provider IN ('lido', 'rocketpool', 'frax', 'coinbase', 'swell', 'etherfi', 'renzo')
GROUP BY provider
ORDER BY total_staked_eth DESC

SELECT 
  source_chain,
  destination_chain,
  SUM(amount_usd) as bridge_volume_usd,
  COUNT(*) as bridge_transactions,
  COUNT(DISTINCT sender) as unique_users
FROM bridges.transfers
WHERE block_time >= NOW() - INTERVAL '7' day
GROUP BY source_chain, destination_chain
ORDER BY bridge_volume_usd DESC
LIMIT 20

SELECT 
  exchange,
  DATE_TRUNC('day', block_time) as date,
  SUM(amount_usd) as daily_deposits_usd,
  COUNT(*) as deposit_count,
  COUNT(DISTINCT sender) as unique_depositors
FROM ethereum.deposits
WHERE exchange IN ('coinbase', 'binance', 'kraken', 'bybit', 'okx')
  AND block_time >= NOW() - INTERVAL '30' day
GROUP BY exchange, date
ORDER BY date, exchange

SELECT 
  category,
  COUNT(DISTINCT project) as protocol_count,
  SUM(volume_usd) as total_volume,
  COUNT(DISTINCT user_address) as unique_users
FROM defi.protocol_stats
WHERE date >= CURRENT_DATE - INTERVAL '30' day
GROUP BY category
ORDER BY total_volume DESC

SELECT 
  DATE_TRUNC('week', first_interaction) as week,
  protocol,
  COUNT(DISTINCT user_address) as new_users
FROM (
  SELECT 
    trader as user_address,
    project as protocol,
    MIN(block_time) as first_interaction
  FROM dex.trades
  WHERE project IN ('uniswap', '1inch')
  GROUP BY trader, project
) user_first_seen
WHERE first_interaction >= NOW() - INTERVAL '180' day
GROUP BY week, protocol
ORDER BY week, protocol

SELECT 
  blockchain,
  <aggregation_metric>
FROM <table>
GROUP BY blockchain

SELECT 
  project as protocol,
  <aggregation_metric>
FROM <table>
WHERE project IN (<list_of_protocols>)
GROUP BY project

SELECT 
  DATE_TRUNC('day', block_time) as date,
  <entity_column>,
  <aggregation_metric>
FROM <table>
GROUP BY date, <entity_column>

SELECT 
  category_type,
  <aggregation_metric>
FROM <table>
WHERE category_type IN ('dex', 'lending', 'bridge', etc.)
GROUP BY category_type