New to Dune? Let’s start your analytics journey by creating an insightful query that analyzes daily DEX trading volumes across different blockchains. This guide will teach you the basics while delivering practical insights about DeFi trading activity.

Getting Started

Create a New Query: Head over to Create New Query to begin. Dune uses DuneSQL, a SQL dialect that is compatible with TrinoSQL. It is a fairly vanilla SQL dialect, but there are some nuances you will eventually encounter. If you are not familiar with SQL, you can learn the basics here.

Choosing the Right Table

Choosing the right table is the first step to creating a successful query. You can find the table you need in the Data Catalog.

  1. Understanding curated data: Instead of working with complex raw blockchain data, we’ll use Dune’s curated data - a collection of pre-built, analytics-ready tables. Curated data standardizes data across chains and makes analysis much easier.

  2. The Perfect Table: For DEX trading analysis, we’ll use the dex.trades table. This table contains standardized trading data across multiple DEXes and blockchains.

Building Your Query Step by Step

Let’s analyze DEX trading activity across different blockchains. We’ll start simple and build up our analysis:

  1. Select Key Metrics: For our DEX analysis, we’ll track:
    • Block date (to see trends over time)
    • Blockchain (to compare activity across networks)
    • Trading volume in USD
    • Number of trades

Here’s our query:

SELECT
  block_date,
  blockchain,
  COUNT(*) as number_of_trades,
  SUM(amount_usd) as trading_volume_usd
FROM dex.trades
WHERE
  block_date >= NOW() - INTERVAL '30' day
GROUP BY
  1, 2
ORDER BY
  1 DESC, 4 DESC

Understanding the Query Components

Each part of this query helps us analyze DEX activity:

  • block_date: Groups all trades into daily buckets
  • blockchain: Helps us compare activity across Ethereum, Polygon, Arbitrum, etc.
  • COUNT(*): Gives us the trading frequency
  • SUM(amount_usd): Shows the total value traded in USD
  • WHERE block_date >= NOW() - INTERVAL '30 days': Focuses on recent activity
  • GROUP BY 1, 2, 3: Combines trades by day, blockchain, and DEX
  • ORDER BY 1 DESC, 5 DESC: Shows newest and highest volume trades first

Making Your Analysis Dynamic with Parameters

Want to focus on your favorite DEX? Let’s add the column project as a parameter to make the analysis interactive:

SELECT
  block_date,
  blockchain,
  project,
  COUNT(*) as number_of_trades,
  SUM(amount_usd) as trading_volume_usd
FROM dex.trades
WHERE
  block_date >= NOW() - INTERVAL '30' day
  AND project = {{Project Name}} -- Try 'uniswap' or 'curve'
GROUP BY
  1, 2, 3
ORDER BY
  1 DESC, 5 DESC

To add the parameter:

  1. Click “Add Parameter” below the query editor
  2. Create a text parameter named “Project Name”
  3. Set default value to “Uniswap”

Now you can instantly switch between different DEXes to compare their performance!

Visualizing Results

After running your query, you can create powerful visualizations:

  1. Click “New Visualization”
  2. Choose “Bar Chart”
  3. Set X-axis to trading_day
  4. Set Y-axis to trading_volume_usd
  5. Group by blockchain

This will create a stacked bar chart showing daily trading volumes by blockchain!

Want to explore more? Try:

  • Removing the project filter to see all DEXes
  • Changing the time period to 7 days or 90 days
  • Adding token filters to focus on specific cryptocurrencies

Next Steps

Congratulations on creating your first DEX analysis query! 🎉

You can find many more queries related to DEX trading activity in this dashboard: DEX Trading Activity

Save your query by clicking Save in the upper right corner. You can revisit and modify it anytime to explore different aspects of DEX trading activity.

Ready to create more advanced queries? Check out our other guides to learn more about Dune