DuneAnalytics for Beginners: How to Query On-Chain Data

DuneAnalytics for Beginners: How to Query On-Chain Data

Understanding the Blockchain Data Landscape

The blockchain is a public ledger, but raw data is virtually unusable. Every transaction, swap, mint, and burn is recorded as a hexadecimal string of numbers and letters. For the average user, this is noise. DuneAnalytics (Dune) solves this by transforming raw blockchain data into structured, queryable tables. It acts as a translator between the blockchain’s native language and SQL (Structured Query Language), the standard language for database interaction. Instead of digging through block explorers one transaction at a time, you can write a query and instantly see aggregated data on any Ethereum Virtual Machine (EVM) compatible chain, including Ethereum, Polygon, BNB Chain, Optimism, Arbitrum, Avalanche, and Gnosis Chain.

Why Dune is Indispensable for Web3 Research

Dune is the industry standard for two reasons: community-submitted spellbooks and the power of SQL. A “spellbook” is Dune’s name for a curated dataset that normalizes raw contract events. For example, the Uniswap spellbook standardizes all Uniswap V2 and V3 swaps into a single table with columns like block_time, token_bought, token_sold, and amount_usd. Without this, you would need to decode every Uniswap contract manually. This normalization allows beginners to write simple queries that would otherwise require days of reverse engineering. The platform is free for public queries, with paid tiers for private dashboards and faster execution.

Anatomy of a Dune Query

Every query in Dune starts with a SELECT statement, but the environment is unique. You are working with data organized by blockchain, then by protocol. The core tables you will use are ethereum.transactions, ethereum.logs, and the spellbook tables like uniswap_v3_ethereum.swaps. A typical beginner query includes:

1. Time Filter: WHERE block_time >= NOW() - INTERVAL '7' DAY
2. Token Filter: AND token_bought = 0x... (address of DAI)
3. Aggregation: SELECT SUM(amount_usd) AS total_volume
4. Grouping: GROUP BY date_trunc('day', block_time)

The most important concept is the UNIX timestamp. Blockchain timestamps are integers (e.g., 1700000000). Dune can convert these to readable dates, but you must use functions like from_unixtime(block_time) or the pre-formatted block_time column.

Step 1: Setting Up Your First Workspace

Navigate to dune.com and sign up using an Ethereum wallet (e.g., MetaMask) or email. Once inside, click “Create” and then “New Query”. The interface has three panels: the query editor (left), the results panel (bottom), and the blockchain/table explorer (right). Before writing any code, use the explorer to find a table. Type “uniswap_v3_ethereum.swaps” into the search bar. Click on it. The explorer will show you the column names, data types, and sample data. This is your reference manual. Click the “Run” button on a sample query provided by Dune to see how results are displayed.

Step 2: Writing Your First Query – Total Daily Volume on Uniswap V3

Let’s write a query that returns the total daily trading volume in USD on Uniswap V3 on Ethereum. Copy this exact code into the editor:

SELECT
    date_trunc('day', block_time) AS day,
    SUM(amount_usd) AS daily_volume_usd
FROM uniswap_v3_ethereum.swaps
WHERE block_time >= NOW() - INTERVAL '30' day
GROUP BY day
ORDER BY day DESC

Press “Run” (or Ctrl+Enter). The results will show 30 rows, each representing a day and its total volume. date_trunc('day', block_time) is a critical function. It rounds every timestamp down to midnight UTC, allowing you to group transactions by day. SUM(amount_usd) adds up the USD value of every swap. NOW() - INTERVAL '30' day filters data to the last 30 days, making the query fast. If you remove that filter, Dune might time out because Uniswap has billions of swaps.

Step 3: Filtering by Specific Tokens

To see volume for a single token, like USDC, you need to know its contract address: 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48. Modify the query:

SELECT
    date_trunc('day', block_time) AS day,
    SUM(amount_usd) AS daily_volume_usd
FROM uniswap_v3_ethereum.swaps
WHERE
    block_time >= NOW() - INTERVAL '30' day
    AND (token_bought = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
         OR token_sold = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48)
GROUP BY day
ORDER BY day DESC

The OR condition is essential because a swap can either buy or sell USDC. If you only use token_bought, you miss half the volume. Always consider both sides of the trade.

Step 4: Joining Tables – Understanding Flash Loans

Advanced queries require joining tables. Imagine you want to know the total amount of ETH borrowed via flash loans on Aave V2. Flash loans are complex because they involve borrowing and repaying in the same transaction. The Aave spellbook (aave_v2_ethereum.flashloans) has the data, but to get the ETH equivalent, you need to join with the price feed. Here is a simplified example:

SELECT
    f.block_time,
    f.amount AS raw_amount,
    f.amount * p.price AS amount_usd
FROM aave_v2_ethereum.flashloans f
LEFT JOIN prices.usd p
    ON p.minute = date_trunc('minute', f.block_time)
    AND p.contract_address = f.asset
WHERE f.block_time >= NOW() - INTERVAL '7' day
LIMIT 10

This query introduces the LEFT JOIN. The prices.usd table stores minute-level token prices. By joining on minute and contract_address, we attach the correct price to each flash loan. Without this, the amount column is in raw token units (e.g., 1 ETH = 1000000000000000000 wei). Always divide by 1e18 for decimals unless the token uses a different decimal count (e.g., USDC has 6 decimals).

Step 5: Using CTEs for Complex Analysis

Common Table Expressions (CTEs) let you build multi-step queries. Suppose you want to find the top 10 wallets that have executed the most swaps on Uniswap V3 in the last 24 hours. A single query is messy. Instead, use a CTE:

WITH swap_counts AS (
    SELECT
        "from" AS wallet_address,
        COUNT(*) AS total_swaps
    FROM ethereum.transactions
    WHERE
        block_time >= NOW() - INTERVAL '1' day
        AND "to" = 0x1F98431c8aD98523631AE4a59f267346ea31F984 -- Uniswap V3 router
        AND success = true
    GROUP BY wallet_address
)

SELECT
    wallet_address,
    total_swaps
FROM swap_counts
ORDER BY total_swaps DESC
LIMIT 10

Here, the CTE swap_counts first aggregates the data, and the final SELECT retrieves the top 10. This is far easier to read and debug than nested subqueries. Note the use of "to" and "from". In SQL, certain words like from or to are reserved. By wrapping them in double quotes, Dune treats them as column names rather than keywords.

Step 6: Visualizing Your Results – From Table to Dashboard

A query result is useless if it’s just a list of numbers. Dune allows you to visualize data directly. After running a query, click the “New Visualization” button below the results. Dune suggests chart types based on your data. For the daily volume query, a bar chart works perfectly. Configure the X-axis as day and Y-axis as daily_volume_usd. You can change colors, add titles, and set time intervals. Once saved, click “Add to Dashboard”. You can create a public dashboard (e.g., “Uniswap V3 Daily Volume”) and pin multiple visualizations. Dashboards auto-refresh, making them suitable for public consumption by the community or your users.

Optimizing Query Performance – Speed and Cost

Dune has resource limits. Queries that scan too much data will timeout or cost more Dune credits (for paid plans). Follow these principles:

• Always Filter by Time: The most common error is forgetting a WHERE clause on block_time. Without it, Dune scans the entire blockchain history, which is thousands of gigabytes.
• Use LIMIT During Development: When testing, always add LIMIT 100 to your query. This returns only 100 rows, allowing you to validate column names and join logic without waiting.
• Leverage Spellbooks: Avoid raw ethereum.logs if a spellbook exists. For example, dex.trades is a spellbook that aggregates all DEX trades across all chains and protocols. Querying dex.trades is often 10x faster than querying individual DEX spellbooks.
• Avoid DISTINCT on Large Datasets: SELECT DISTINCT checks every row for uniqueness. On a 100-million-row table, this is painfully slow. Instead, use GROUP BY with an aggregation function.
• Use DECIMAL Division for Accuracy: When calculating ratios or percentages, cast integers to decimals to avoid integer truncation. For example: CAST(count AS DOUBLE) / CAST(total AS DOUBLE) * 100.

Real-World Example – Tracking a Specific Address

Let’s say you want to track all activity of a known MEV bot address: 0x0000000000007f150bd6f54c40a34d7b3c5c1c8e. You want to see every transaction it sent and received in the last 7 days.

WITH sent_tx AS (
    SELECT
        hash,
        block_time,
        "from" AS sender,
        value / 1e18 AS eth_sent
    FROM ethereum.transactions
    WHERE "from" = 0x0000000000007f150bd6f54c40a34d7b3c5c1c8e
        AND block_time >= NOW() - INTERVAL '7' day
),
received_tx AS (
    SELECT
        hash,
        block_time,
        "to" AS receiver,
        value / 1e18 AS eth_received
    FROM ethereum.transactions
    WHERE "to" = 0x0000000000007f150bd6f54c40a34d7b3c5c1c8e
        AND block_time >= NOW() - INTERVAL '7' day
)
SELECT * FROM sent_tx
UNION ALL
SELECT * FROM received_tx
ORDER BY block_time DESC
LIMIT 100

This query uses UNION ALL to combine two CTEs. UNION ALL keeps duplicate rows (if any), while UNION removes them but is slower. Since transaction hashes are unique, UNION ALL is safe and fast.

Common Pitfalls and How to Avoid Them

• Decimal Mismatch: ERC-20 tokens have varying decimals (USDC=6, WBTC=8, ETH=18). When dividing by 1e18 for an 8-decimal token, your numbers will be off by a factor of 10^10. Use the decimals column from tokens.ethereum if available.
• Null Values in Joins: If a token has no price entry at a specific minute, the LEFT JOIN produces NULL. Always use COALESCE(amount, 0) or filter with WHERE p.price IS NOT NULL.
• Case Sensitivity: Ethereum addresses are case-insensitive on the blockchain but case-sensitive in Dune SQL. Always use lowercase addresses (e.g., 0xabc... not 0xABC...).
• Overlapping Spellbooks: Different spellbooks may cover the same protocol (e.g., uniswap_v3_ethereum.swaps vs dex.trades). Results can differ due to different normalization rules. Choose one and stick with it for consistency.

Integration with External Tools

Dune offers a REST API (paid) that allows you to pull query results into Python, Google Sheets, or dashboards like Grafana. For beginners, the easiest integration is the “Download CSV” button on any query result. You can also use the “Embed” feature to place a live Dune chart on your website using an iframe. For programmatic access, Python libraries like dune-client simplify authentication and data retrieval. This is useful if you want to feed on-chain data into a machine learning model or a custom reporting tool.

Exploring Beyond EVM Chains

Dune now supports Solana, although the data structure differs. Solana uses accounts rather than contracts. The key tables are solana.transactions and solana.instruction_calls. Queries look similar, but you will use account addresses instead of contract addresses. For example, to get transactions involving the Raydium DEX on Solana:

SELECT
    block_time,
    signer,
    fee / 1e9 AS fee_sol
FROM solana.transactions
WHERE account_keys LIKE '%Raydium_router_address%'
LIMIT 100

The LIKE operator on account_keys allows you to filter transactions that touched a specific program. This is less precise than EVM’s smart contract filtering but is the standard approach for Solana.

Maintaining Your Queries Over Time

Blockchain data is append-only. As new blocks are added, your queries automatically include the latest data. However, spellbooks (normalized tables) are sometimes updated with new columns or corrections. Dune notifies users via a banner in the query editor when a spellbook has a breaking change. To future-proof your queries:

• Avoid using SELECT *; always list specific columns by name.
• Never hardcode block numbers or timestamps. Use NOW() - INTERVAL for relative time windows.
• Monitor the Dune GitHub repository for spellbook change logs.

Building a Portfolio of Dashboards

A single query is a needle. A dashboard is a quilt. As a beginner, start with one query that answers a single question, like “What is the daily volume of USDC on Polygon?” Once that query is clean and visualized, duplicate it for other tokens or chains. Then create a dashboard that shows a comparison: Volume of USDC vs USDT vs DAI. Use Dune’s parameter feature to make the dashboard interactive. For example, add a dropdown labeled “Token” that lets viewers select which token to view. Parameters are written as {{Token}} in the SQL. This transforms a static dashboard into a dynamic tool.

Leveraging the Dune Community for Learning

Dune has a massive library of public dashboards and queries. Use the “Explore” page to find a dashboard you admire (e.g., “Total Value Locked across DeFi”). Open the underlying queries by clicking the “Query” tab. Read the SQL. See how the author handled joins, filtering, and time aggregation. This is the fastest way to learn advanced patterns. You can also fork any public query by clicking “Fork” in the editor. This creates a copy you can modify without affecting the original. Participate in the Dune Discord community; users frequently share SQL tips and debugging help.

Practical Workflow for Daily Use

1. 9:00 AM: Open your personal dashboard. It auto-refreshes, showing overnight on-chain activity.
2. 9:30 AM: Write a new query to investigate a suspicious whale wallet. Use CTEs to break down the wallet’s interactions with Aave, Compound, and Uniswap.
3. 10:00 AM: Export the query as a CSV and load it into a Google Sheet for team review.
4. 2:00 PM: Fork a community query for a new token launch. Modify the token address and add a 24-hour time filter.
5. 5:00 PM: Create a new visualization (area chart) showing cumulative volume. Pin it to the dashboard.

Final Technical Notes on SQL Syntax

Dune uses the Presto/Trino SQL dialect. This means certain functions differ from MySQL or PostgreSQL. For example:

• String concatenation uses || not +.
• Date functions use date_add('day', -1, NOW()) not NOW() - INTERVAL 1 DAY (though the latter is also supported).
• Array functions include array_agg() for collecting values into a list.
• The UNNEST function is used to explode arrays into rows, useful for analyzing token transfers.

Mastering these subtle differences will prevent frustrating syntax errors.