How to use Dune Analytics to query and analyze on-chain data from Ethereum

Understanding on-chain data is crucial for analytics engineers, data engineers, data analysts, or web3 enthusiasts who constantly seek meaningful insights. On-chain data provides invaluable insights into blockchain activities. This is where Dune Analytics comes in.

But what exactly is Dune Analytics, and why should you use it for Ethereum data? Today, we’ll be exploring how you can leverage Dune Analytics to explore transaction counts, gas usage, and popular smart contracts on Ethereum.

Photo from Collective Sift

🔍 What is Dune Analytics?

Dune Analytics is a powerful, community-driven platform that allows users to query, analyze, and visualize blockchain data. Unlike traditional analytics tools, Dune is tailored specifically for blockchain data, making it a go-to choice for crypto enthusiasts and professionals. It's like having a massive, pre-indexed blockchain database at your fingertips, ready for you to explore and analyze.

🚀 Why Use Dune Analytics for Ethereum Data?

Ethereum, being one of the most widely used blockchains, generates massive amounts of data. Analyzing this data can uncover trends, reveal insights into token movements, and monitor decentralized finance (DeFi) protocols. Dune Analytics simplifies this process by providing a user-friendly interface and robust querying capabilities.

🛠️ Getting Started with Dune Analytics

First things first, you'll need an account.

Head over to the Dune Analytics website and sign up. It's free and straightforward. Once you have your account, you'll be greeted with a dashboard filled with tons of data at your fingertips.

📊 Querying Ethereum Data: Let's Get Our Hands Dirty

Now, let's explore some practical examples of how to use Dune Analytics to query Ethereum data.

1. Transaction Counts: The Pulse of Ethereum

Let's start with a simple query to get the daily transaction count on Ethereum for the past 30 days:

SELECT
    date_trunc('day', block_time) AS date,
    COUNT(*) AS daily_transactions
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1;

This query will give you a day-by-day breakdown of transaction volumes, allowing you to spot trends and patterns in Ethereum usage.

2. Gas Usage: The Cost of Doing Business

Gas usage is a critical metric in Ethereum. Here's a query to analyze average daily gas prices:

SELECT
    date_trunc('day', block_time) AS date,
    AVG(gas_price) / 1e9 AS avg_gas_price_gwei
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1;

This query will help you understand how gas prices fluctuate over time, which is crucial for both users and developers in the Ethereum ecosystem.

3. Popular Smart Contracts: Who's Making Waves?

To identify the most active smart contracts, we can look at the contracts with the most interactions:

SELECT
    to AS contract_address,
    COUNT(*) AS interaction_count
FROM ethereum.transactions
WHERE block_time >= NOW() - INTERVAL '7' DAY
    AND to IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

This query will show you the top 10 most interacted-with contract addresses over the past week, giving you insights into which dApps or protocols are currently popular.

🛠️ Advanced Techniques: Leveling Up Your Dune Game

As you become more comfortable with Dune Analytics, you can start to combine these queries and create more complex analyses. Here are some advanced techniques to try:

1. Joining Tables for Richer Insights

Let's enhance our popular contracts query by joining with the contracts table to get more information:

WITH popular_contracts AS (
  SELECT
    to AS contract_address,
    COUNT(*) AS interaction_count
  FROM ethereum.transactions
  WHERE
    block_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY AND NOT to IS NULL
  GROUP BY
    to
  ORDER BY
    interaction_count DESC
  LIMIT 10
)
SELECT
  pc.contract_address,
  pc.interaction_count,
  c.name AS contract_name,
  c.created_at AS contract_created_at
FROM popular_contracts AS pc
LEFT JOIN ethereum.contracts AS c
  ON pc.contract_address = c.address
ORDER BY
  pc.interaction_count DESC;

This query identifies the top 10 most interacted-with contracts in the last 7 days and joins this information with the contracts table to get the contract name, creator, and creation date. The LEFT JOIN ensures we get all popular contracts, even if some aren't in the contracts table.

2. Using CTEs for Complex Queries

Common Table Expressions (CTEs) allow you to break down complex queries into more manageable parts. Here's an example that combines transaction counts, gas usage, and unique active addresses:

WITH daily_transactions AS (
    SELECT
        date_trunc('day', block_time) AS date,
        COUNT(*) AS transaction_count
    FROM ethereum.transactions
    WHERE block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
daily_gas_usage AS (
    SELECT
        date_trunc('day', block_time) AS date,
        AVG(gas_price) / 1e9 AS avg_gas_price_gwei,
        SUM(gas_used) AS total_gas_used
    FROM ethereum.transactions
    WHERE block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
),
daily_active_addresses AS (
    SELECT
        date_trunc('day', block_time) AS date,
        COUNT(DISTINCT "from") AS unique_senders
    FROM ethereum.transactions
    WHERE block_time >= NOW() - INTERVAL '30' DAY
    GROUP BY 1
)
SELECT
    dt.date,
    dt.transaction_count,
    dg.avg_gas_price_gwei,
    dg.total_gas_used,
    dt.transaction_count * dg.avg_gas_price_gwei AS estimated_total_gas_cost_gwei,
    daa.unique_senders
FROM daily_transactions dt
JOIN daily_gas_usage dg ON dt.date = dg.date
JOIN daily_active_addresses daa ON dt.date = daa.date
ORDER BY dt.date;

This query creates three CTEs:

1. daily_transactions for transaction counts

2. daily_gas_usage for gas price and usage

3. daily_active_addresses for unique active addresses

It then joins these CTEs to provide a comprehensive daily overview of network activity, including an estimated total gas cost.

3. Calculating Ether Volume

When working with Ethereum data, it's often necessary to convert values from Wei (the smallest unit of Ether) to Ether. Here's an example of how to calculate the daily Ether volume:

SELECT
  DATE_TRUNC('day', block_time) AS date,
  SUM(value) / TRY_CAST(POWER(10, 18) AS DECIMAL(38, 0)) AS daily_ether_volume
FROM ethereum.transactions
WHERE
  block_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY
  DATE_TRUNC('day', block_time)
ORDER BY
  DATE_TRUNC('day', block_time);

This query gives you a daily breakdown of the total volume of Ether transferred on the Ethereum network over the past 30 days. It's a great way to visualize network activity and identify trends or anomalies in transaction volumes.

🧭 Practical Applications: From Data to Insights

The power of Dune Analytics lies not just in querying data, but in the insights you can derive. Here are some ways you can apply these queries:

1. Track the adoption of new DeFi protocols by monitoring transaction volumes to their contract addresses.

2. Analyze gas price trends to optimize transaction timing for your dApp users.

3. Identify emerging trends in the Ethereum ecosystem by monitoring which contracts are gaining popularity.

4. Assess the impact of network upgrades or major events on transaction volumes and gas prices.

5. Compare the activity of different DeFi protocols or NFT projects over time.

6. Analyze the relationship between gas prices, transaction volumes, and the number of active addresses.

🚀 Taking It Further: Building Your Own Dashboard

Dune Analytics allows you to save your queries and combine them into customized dashboards. This is where the magic really happens - you can create a comprehensive view of the Ethereum ecosystem tailored to your specific interests or projects.

🎯 Summary

Remember, the key to success with Dune Analytics is curiosity and experimentation. Don't be afraid to modify these queries, combine them in new ways, or dive into Dune's extensive query library to see what others in the community are exploring. The more you practice, the more proficient you'll become at extracting meaningful insights from the wealth of data available on the Ethereum blockchain.

By mastering these advanced techniques - joining tables, using CTEs, and leveraging built-in functions - you'll be well-equipped to tackle complex analyses and uncover deeper insights about the Ethereum ecosystem.

As you continue your journey with Dune Analytics, consider joining the Dune community forums or participating in their Discord channel. Engaging with other analysts can provide new perspectives, help you learn advanced techniques, and even lead to collaborative projects.

Happy querying, and may your data always be insightful!

---

References

1. Dune Analytics. "Dune Analytics Documentation." https://dune.com/docs/

2. Dune Analytics. "YouTube Tutorial."