How to Analyze On-Chain Data With Dune Analytics

Professional crypto traders use on-chain data to find edge before it shows up in price. Dune Analytics makes this accessible to anyone who knows basic SQL. Here's how to use it.

What is Dune Analytics?

Dune is a blockchain analytics platform. It indexes all Ethereum (and many other chain) transaction data into SQL tables you can query. You can find:

• Which wallets are accumulating a token
• How much capital is flowing into/out of protocols
• Which DeFi protocols are gaining/losing users
• Large unusual transfers before price moves

All free, all public, all queryable.

Getting Started

1. Create a free account at dune.com
2. Click "New Query"
3. Write SQL against blockchain tables
4. Create dashboards from query results

Essential Tables

-- Token transfers (ERC-20)
SELECT * FROM erc20_ethereum.evt_Transfer LIMIT 10;

-- Uniswap v3 swaps
SELECT * FROM uniswap_v3_ethereum.Swap LIMIT 10;

-- Aave deposits/borrows
SELECT * FROM aave_v3_ethereum.evt_Supply LIMIT 10;

-- ETH transactions
SELECT * FROM ethereum.transactions LIMIT 10;

Useful Queries to Start

Find Large USDC Transfers (Whale Watching)

SELECT 
    date_trunc('hour', evt_block_time) AS hour,
    "from",
    "to",
    value / 1e6 AS usdc_amount,
    evt_tx_hash
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = 0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48  -- USDC
    AND value / 1e6 > 1000000  -- More than $1M
    AND evt_block_time > NOW() - INTERVAL '24 hours'
ORDER BY usdc_amount DESC
LIMIT 20;

DEX Volume by Protocol (Daily)

SELECT
    project,
    SUM(amount_usd) AS daily_volume_usd,
    COUNT(*) AS trades
FROM dex.trades
WHERE block_time > NOW() - INTERVAL '1 day'
    AND blockchain = 'ethereum'
GROUP BY project
ORDER BY daily_volume_usd DESC;

Token Holder Distribution

WITH balances AS (
    SELECT
        "to" AS address,
        SUM(value / 1e18) AS balance
    FROM erc20_ethereum.evt_Transfer
    WHERE contract_address = 0x... -- Your token
    GROUP BY 1
)
SELECT
    CASE 
        WHEN balance > 1000000 THEN 'Whale (>1M)'
        WHEN balance > 100000 THEN 'Large (100k-1M)'
        WHEN balance > 10000 THEN 'Medium (10k-100k)'
        ELSE 'Small (<10k)'
    END AS holder_tier,
    COUNT(*) AS holder_count,
    SUM(balance) AS total_held,
    SUM(balance) / SUM(SUM(balance)) OVER () * 100 AS pct_supply
FROM balances
WHERE balance > 0
GROUP BY 1
ORDER BY 3 DESC;

Aave Liquidation Risk Monitor

-- Find positions close to liquidation on Aave
SELECT
    user,
    reserve,
    current_variable_debt / 1e6 AS debt_usdc,
    health_factor / 1e18 AS health_factor
FROM aave_v3_ethereum.borrow_history_materialized
WHERE health_factor / 1e18 < 1.2  -- Less than 1.0 gets liquidated
ORDER BY health_factor ASC
LIMIT 50;

Using Dune API for Automated Monitoring

import requests
import os

DUNE_API_KEY = os.getenv("DUNE_API_KEY")

def run_query(query_id: int) -> list[dict]:
    """Execute a saved Dune query and get results."""
    # Execute query
    exec_r = requests.post(
        f"https://api.dune.com/api/v1/query/{query_id}/execute",
        headers={"x-dune-api-key": DUNE_API_KEY},
        json={"performance": "medium"}
    )
    execution_id = exec_r.json()['execution_id']
    
    # Wait for results (poll)
    import time
    while True:
        status_r = requests.get(
            f"https://api.dune.com/api/v1/execution/{execution_id}/status",
            headers={"x-dune-api-key": DUNE_API_KEY}
        )
        status = status_r.json()['state']
        
        if status == 'QUERY_STATE_COMPLETED':
            break
        elif status == 'QUERY_STATE_FAILED':
            return []
        
        time.sleep(5)
    
    # Get results
    results_r = requests.get(
        f"https://api.dune.com/api/v1/execution/{execution_id}/results",
        headers={"x-dune-api-key": DUNE_API_KEY}
    )
    return results_r.json()['result']['rows']

# Run a saved "whale activity" query
whale_data = run_query(YOUR_QUERY_ID)
large_transfers = [row for row in whale_data if row['usdc_amount'] > 5_000_000]
print(f"Found {len(large_transfers)} transactions >$5M in last 24h")

Alpha Signals From On-Chain Data

Accumulation: When wallets that previously held large amounts are buying again, often precedes price moves.

Protocol TVL flows: Rapid TVL increase in a DeFi protocol often indicates genuine user growth (bullish for the token).

Exchange outflows: BTC/ETH moving off exchanges to cold wallets = reduced sell pressure = potentially bullish.

Funding rate + open interest: Rising OI + positive funding = leveraged longs dominating = watch for squeeze.

On-chain analytics is a deep rabbit hole. Dune's community has thousands of free dashboards already built that you can fork and customize. Start exploring, and you'll find edge that pure chart-reading misses.