Analyze Onchain Data

Execute SQL queries and build custom analytics pipelines with Dune’s query execution engine

Overview

Perfect for data teams, researchers, and analysts who need to programmatically analyze onchain data, build custom reports, and create automated analytics pipelines.

What You’ll Learn

• Execute SQL queries via API
• Retrieve and process results
• Handle query parameters
• Build analytics pipelines
• Filter and transform data

Prerequisites

• A Dune account (create one free)
• An API key (get your API key)
• Basic familiarity with SQL

Quick Start

1. Execute a Simple Query

Let’s start by executing a query to analyze recent DEX trading volume:

Python

from dune_client.client import DuneClient
import pandas as pd

# Initialize client
dune = DuneClient(api_key="YOUR_API_KEY")

# Execute SQL query
sql = """
SELECT 
    blockchain,
    DATE_TRUNC('hour', block_time) as hour,
    SUM(amount_usd) as volume_usd,
    COUNT(*) as trade_count
FROM dex.trades
WHERE block_time > now() - interval '24' hour
GROUP BY 1, 2
ORDER BY 2 DESC
"""

results = dune.run_sql(query_sql=sql)

# Convert to pandas DataFrame for analysis
df = pd.DataFrame(results.result.rows)
print(df.head())

# Step 1: Execute query
curl -X POST "https://api.dune.com/api/v1/sql/execute" \
  -H "Content-Type: application/json" \
  -H "X-Dune-Api-Key: YOUR_API_KEY" \
  -d '{
    "sql": "SELECT blockchain, DATE_TRUNC('\''hour'\'', block_time) as hour, SUM(amount_usd) as volume_usd, COUNT(*) as trade_count FROM dex.trades WHERE block_time > now() - interval '\''24'\'' hour GROUP BY 1, 2 ORDER BY 2 DESC",
    "performance": "medium"
  }'

# Response includes execution_id
# {"execution_id":"01JA...","state":"QUERY_STATE_EXECUTING"}

# Step 2: Get results (after query completes)
curl "https://api.dune.com/api/v1/execution/{execution_id}/results" \
  -H "X-Dune-Api-Key: YOUR_API_KEY"

The run_sql() function in our Python SDK automatically handles query execution and polling for completion. With cURL, you’ll need to poll the execution status endpoint and then fetch results manually.

2. Use Parameterized Queries

For reusable analytics, create queries with parameters:

Python

from dune_client.client import DuneClient
from dune_client.query import QueryBase
from datetime import datetime, timedelta

dune = DuneClient(api_key="YOUR_API_KEY")

# Execute a saved query with parameters
query_id = 3493826  # Example: Popular DEX analysis query

query = QueryBase(query_id=query_id)
results = dune.run_query(query=query)

# Process results
for row in results.result.rows:
    protocol = row.get('protocol') or row.get('project', 'Unknown')
    volume = row.get('volume') or row.get('volume_usd', 0)
    print(f"{protocol}: ${volume:,.2f}")

import { DuneClient, QueryParameter } from '@duneanalytics/client-sdk';

const client = new DuneClient(process.env.DUNE_API_KEY ?? '');

// Execute a saved query with parameters
const queryId = 3493826;

const params = {
  query_parameters: [
    QueryParameter.text("blockchain", "ethereum"),
    QueryParameter.number("min_volume", 1000),
    QueryParameter.number("days", 7),
  ]
};

client
  .runQuery(queryId, params)
  .then((executionResult) => {
    // Process results
    executionResult.result?.rows.forEach(row => {
      console.log(`${row.protocol}: $${row.volume.toLocaleString()}`);
    });
  });

3. Build an Analytics Pipeline

Create a pipeline that runs multiple queries and combines results:

from dune_client.client import DuneClient
import pandas as pd
from datetime import datetime

dune = DuneClient(api_key="YOUR_API_KEY")

def daily_dex_analysis():
    """Run daily DEX analytics pipeline"""
    
    # 1. Get overall volume
    volume_query = """
    SELECT 
        SUM(amount_usd) as total_volume,
        COUNT(DISTINCT taker) as unique_traders
    FROM dex.trades
    WHERE block_time > now() - interval '24' hour
    """
    volume_data = dune.run_sql(query_sql=volume_query)
    
    # 2. Get top protocols
    protocol_query = """
    SELECT 
        project as protocol,
        SUM(amount_usd) as volume,
        COUNT(*) as trades
    FROM dex.trades
    WHERE block_time > now() - interval '24' hour
    GROUP BY 1
    ORDER BY 2 DESC
    LIMIT 10
    """
    protocol_data = dune.run_sql(query_sql=protocol_query)
    
    # 3. Get chain breakdown
    chain_query = """
    SELECT 
        blockchain,
        SUM(amount_usd) as volume
    FROM dex.trades
    WHERE block_time > now() - interval '24' hour
    GROUP BY 1
    ORDER BY 2 DESC
    """
    chain_data = dune.run_sql(query_sql=chain_query)
    
    # Combine and process results
    report = {
        'timestamp': datetime.now(),
        'total_volume': volume_data.result.rows[0]['total_volume'],
        'unique_traders': volume_data.result.rows[0]['unique_traders'],
        'top_protocols': pd.DataFrame(protocol_data.result.rows),
        'chain_breakdown': pd.DataFrame(chain_data.result.rows)
    }
    
    return report

# Run pipeline
report = daily_dex_analysis()
print(f"24h Volume: ${report['total_volume']:,.2f}")
print(f"Unique Traders: {report['unique_traders']:,}")
print("\nTop Protocols:")
print(report['top_protocols'])

4. Filter and Transform Results

Use Dune’s powerful filtering to refine results:

Python

from dune_client.client import DuneClient

dune = DuneClient(api_key="YOUR_API_KEY")

# Execute query with SQL-based filtering
sql = """
SELECT 
    blockchain,
    project,
    SUM(amount_usd) as volume_usd
FROM dex.trades
WHERE block_time > now() - interval '24' hour
    AND blockchain = 'ethereum'
    AND amount_usd > 1000000
GROUP BY 1, 2
ORDER BY 3 DESC
"""

results = dune.run_sql(query_sql=sql)

# Results are already filtered server-side
for row in results.result.rows:
    print(row)

# Execute query, then filter results
curl "https://api.dune.com/api/v1/execution/{execution_id}/results?filters=blockchain%20%3D%20%27ethereum%27%20AND%20volume_usd%20%3E%201000000" \
  -H "X-Dune-Api-Key: YOUR_API_KEY"

Server-side filtering with Dune’s SQL-like WHERE clause syntax is unique to Dune’s API! It saves bandwidth and processing time compared to filtering client-side.

Common Patterns

Scheduled Analytics

Run analytics on a schedule (e.g., with cron, Airflow, or cloud scheduler):

import schedule
import time
from dune_client.client import DuneClient

dune = DuneClient(api_key="YOUR_API_KEY")

def hourly_report():
    """Generate hourly DEX report"""
    sql = """
    SELECT 
        project,
        SUM(amount_usd) as hourly_volume
    FROM dex.trades
    WHERE block_time > now() - interval '1' hour
    GROUP BY 1
    ORDER BY 2 DESC
    """
    
    results = dune.run_sql(query_sql=sql)
    
    # Send to your dashboard/database/Slack
    send_to_dashboard(results.result.rows)

# Run every hour
schedule.every().hour.at(":00").do(hourly_report)

while True:
    schedule.run_pending()
    time.sleep(60)

Onchain Monitoring

Monitor specific metrics and trigger alerts:

from dune_client.client import DuneClient
import time

dune = DuneClient(api_key="YOUR_API_KEY")

def monitor_large_trades(threshold=1000000):
    """Alert on large trades"""
    sql = f"""
    SELECT *
    FROM dex.trades
    WHERE block_time > now() - interval '5' minute
    AND amount_usd > {threshold}
    ORDER BY block_time DESC
    """
    
    results = dune.run_sql(query_sql=sql)
    
    if results.result.rows:
        for trade in results.result.rows:
            send_alert(f"Large trade: ${trade['amount_usd']:,.2f} on {trade['blockchain']}")

# Run continuously
while True:
    monitor_large_trades()
    time.sleep(300)  # Check every 5 minutes

Next Steps

Execute Queries API

Full API reference for query execution

Result Filtering

Advanced filtering techniques

Materialized Views

Create and manage materialized views

DuneSQL Functions

Learn DuneSQL functions and operators

Example Use Cases

• Research Reports: Generate daily/weekly reports on DeFi protocols, NFT markets, or chain activity
• Automated Dashboards: Feed data to custom dashboards or BI tools
• Alert Systems: Monitor metrics and trigger alerts when thresholds are met
• Comparative Analysis: Compare metrics across chains, protocols, or time periods