Dune Analytics SQL Mastery

From Raw Data to Rich Insights

You already know that blockchains are ledgers of transactions. But the real story isn't just that a transaction happened; it's what that transaction did. Did it swap a token, mint an NFT, or lend assets? Answering these questions requires digging into the data, and that's where Dune Analytics comes in.

Dune transforms the cryptic, raw data from blockchain nodes into structured, human-readable SQL tables. Instead of parsing raw transaction logs, you can directly query specific actions, like all the Transfer events from a particular token contract. This process relies on decoding smart contract data, which is a massive leap from looking at raw block explorers.

Querying Decoded Tables

The core of Dune's power lies in its decoded tables which represent smart contract events (logs) and internal function calls (traces). When a developer writes a smart contract, they define specific events that the contract can emit, like Swap, Deposit, or Mint. Dune listens for these events and neatly organizes them into tables you can query.

For example, every time an ERC-721 NFT is transferred, the contract emits a Transfer event. This event contains the sender's address (from), the receiver's address (to), and the unique tokenId of the NFT. Dune captures this and puts it into a table for that specific NFT collection.

Let's find the most recent 10 transfers for the Bored Ape Yacht Club (BAYC) contract on Ethereum. We can query the logs table, filtering for the specific Transfer event signature and the BAYC contract address.

SELECT 
    evt_tx_hash, 
    "from", 
    "to", 
    tokenId
FROM bayc_ethereum.BoredApeYachtClub_evt_Transfer
ORDER BY evt_block_time DESC
LIMIT 10;

Connecting Dots with Joins

A single user action, like swapping on a decentralized exchange (DEX), often involves multiple smart contract events. The user might transfer tokens into the DEX contract, which then triggers a Swap event, followed by another Transfer event that sends the new tokens back to the user. Each event is a piece of the puzzle.

To build a complete picture, you need to connect these pieces using SQL JOINs. By joining different event tables on a shared tx_hash (transaction hash), you can trace a user's entire journey within a single transaction. This is how you move from seeing isolated events to understanding complex behaviors.

-- Find all swaps on Uniswap V3 that also involved Wrapped Ether (WETH)
-- We join the Swap event with the Transfer event to correlate them
SELECT
    s.tx_hash,
    s.amount0,
    s.amount1
FROM uniswap_v3_ethereum.Pair_evt_Swap s
INNER JOIN erc20_ethereum.evt_Transfer t 
    ON s.evt_tx_hash = t.evt_tx_hash
WHERE t.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 -- WETH address
LIMIT 100;

Aggregating Data and Using Abstractions

Tracking single transactions is useful, but the real power comes from seeing the big picture. Using aggregate functions like SUM(), COUNT(), and GROUP BY, you can answer high-level questions like:

What was the total daily trading volume for a specific DEX?
How many unique users minted an NFT from a collection?
Who are the top 10 traders by volume in the last week?

Writing these queries from scratch can be complex. You have to find contract addresses, deal with different token decimal places, and join multiple tables. To simplify this, the Dune community has built —high-level, cleaned-up tables that combine raw data from multiple sources into a single, easy-to-query view.

Instead of writing a complex query to calculate Uniswap's daily volume from raw event logs, you can use the dex.trades abstraction.

-- Calculate daily trading volume on Uniswap using an abstraction
SELECT
    date_trunc('day', block_time) AS day,
    SUM(usd_amount) AS daily_volume
FROM dex.trades
WHERE project = 'uniswap'
GROUP BY 1
ORDER BY 1 DESC
LIMIT 30;

This query is far simpler and less error-prone than one built from scratch. Whenever possible, start with an abstraction.

Visualizing Your Findings

A table of numbers is just the beginning. The final step is to turn your query results into charts and dashboards to tell a story. Dune has a powerful visualization engine built-in. Once your query runs, you can create a new visualization, choosing from bar charts, line charts, pie charts, and more.

For example, the daily volume query above is a perfect candidate for a bar chart. You would set the x-axis to the day column and the y-axis to the daily_volume column. You can then add this chart to a dashboard, combining multiple queries to create a comprehensive overview of a protocol or ecosystem.

Ready to test your knowledge? Let's see if you can apply these concepts.

Quiz Questions 1/5

What is the primary function of Dune Analytics?

Quiz Questions 2/5

What are Dune's "decoded tables" primarily based on?

Mastering SQL on Dune is a journey from understanding individual events to synthesizing broad market trends. By combining decoded tables, powerful joins, and time-saving abstractions, you can uncover insights that are simply not visible on the surface of the blockchain.

Advanced On Chain Data Mastery for Researchers