Common Patterns

SELECT project, sum(amount_usd) AS volume_usd
FROM agent.ethereum_dex_trades
WHERE block_date = today() - 1
GROUP BY project
ORDER BY volume_usd DESC
LIMIT 10

SELECT block_date, sum(amount_usd) AS volume_usd, count() AS trades
FROM agent.ethereum_dex_trades
WHERE token_pair = 'USDC-WETH'
  AND block_date >= today() - 30
GROUP BY block_date
ORDER BY block_date

SELECT block_date, price
FROM agent.ethereum_prices_day
WHERE symbol = 'WETH'
  AND source = 'coingecko'
  AND block_date >= today() - 30
ORDER BY block_date

SELECT block_date, price, source
FROM agent.ethereum_prices_day
WHERE contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' -- USDC
  AND block_date >= today() - 7
ORDER BY block_date, source

SELECT block_time, `from`, `to`, amount, amount_usd
FROM agent.ethereum_transfers
WHERE symbol = 'USDC'
  AND block_date = today() - 1
ORDER BY amount_usd DESC
LIMIT 20

SELECT
  sum(if(`to` = '0x...your_address...', amount_usd, 0)) AS inflow,
  sum(if(`from` = '0x...your_address...', amount_usd, 0)) AS outflow
FROM agent.ethereum_transfers
WHERE block_date >= today() - 7
  AND (`from` = '0x...your_address...' OR `to` = '0x...your_address...')

SELECT project, version, tvl_usd
FROM agent.ethereum_tvl_daily
WHERE block_date = (SELECT max(block_date) FROM agent.ethereum_tvl_daily)
ORDER BY tvl_usd DESC
LIMIT 20

SELECT project, sum(daily_fees_usd) AS weekly_fees
FROM agent.ethereum_fees_daily
WHERE block_date >= today() - 7
GROUP BY project
ORDER BY weekly_fees DESC
LIMIT 10

SELECT project, symbol, pool_meta, apy, tvl_usd
FROM agent.ethereum_yields_daily
WHERE block_date = (SELECT max(block_date) FROM agent.ethereum_yields_daily)
  AND tvl_usd > 1000000
ORDER BY apy DESC
LIMIT 20

SELECT date, source,
       sum(notional_volume_usd) AS volume,
       sum(open_interest_usd) AS oi
FROM agent.prediction_markets_daily
WHERE date >= today() - 30
GROUP BY date, source
ORDER BY date DESC, source

SELECT condition_id, question, category,
       volume_total, active
FROM agent.polymarket_market_details
WHERE question ILIKE '%bitcoin%100k%'
  AND outcome_index = 0
ORDER BY volume_total DESC
LIMIT 10

SELECT
  condition_id,
  argMax(open_interest_usd, block_date) AS latest_oi
FROM agent.polymarket_open_interest_daily
GROUP BY condition_id
ORDER BY latest_oi DESC
LIMIT 20

SELECT coin, funding_rate,
       funding_rate * 8760 AS annualized
FROM agent.hyperliquid_funding_rates FINAL
WHERE funding_date = today() - 1
ORDER BY abs(funding_rate) DESC
LIMIT 20

SELECT coin, open_interest * mark_px AS oi_usd, day_ntl_vlm
FROM agent.hyperliquid_market_data FINAL
WHERE snapshot_date = today() - 1
ORDER BY oi_usd DESC
LIMIT 20

SELECT 'ethereum' AS chain, block_date, active_senders
FROM agent.ethereum_chain_daily WHERE block_date >= today() - 7
UNION ALL
SELECT 'base', block_date, active_senders
FROM agent.base_chain_daily WHERE block_date >= today() - 7
UNION ALL
SELECT 'arbitrum', block_date, active_senders
FROM agent.arbitrum_chain_daily WHERE block_date >= today() - 7
ORDER BY block_date, chain