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.

🔍 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:
daily_transactions
for transaction countsdaily_gas_usage
for gas price and usagedaily_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:
Track the adoption of new DeFi protocols by monitoring transaction volumes to their contract addresses.
Analyze gas price trends to optimize transaction timing for your dApp users.
Identify emerging trends in the Ethereum ecosystem by monitoring which contracts are gaining popularity.
Assess the impact of network upgrades or major events on transaction volumes and gas prices.
Compare the activity of different DeFi protocols or NFT projects over time.
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
Dune Analytics. "Dune Analytics Documentation." https://dune.com/docs/
Dune Analytics. "YouTube Tutorial."