HowtogetstartedwithqueryingonDuneAnalytics

This post goes over some basic examples of how to search and write basic queries as well as visualize them with graphs on Dune Analytics. No prior knowledge needed and the opportunities for exploration are limitless.

This is a guest post by Alex Manuskin. It was first published on Medium and has been slightly updated before publishing here.

If you prefer a video tutorial you can check out this video.

Dune Analytics is a powerful tool for blockchain research. It can be used to query, extract, and visualize vast amounts of data on the Ethereum blockchain.

Dex Volume percentage (source)

In public blockchains such as Ethereum, all the information is inherently public. All you need is to look for it. So far, answering questions such as how many users does a project has, or what is the daily volume of a DEX, would most likely require writing a specialised script. Running the script would involve iterating over blocks, parsing the information, properly sorting it, and extracting the data.

This can be both time-consuming as well as extremely specialised. Scripts like that would likely be able to extract information about one specific project but would require extensive modifications to generalise for anything else. Besides, running on all blocks is a long process in itself, that requires either a full node or many individual queries to an external service.

Dune Analytics is a tool that greatly simplifies the process. It is a web-based platform for querying Ethereum data by using simple SQL queries, from pre-populated databases. Instead of writing a specialized script, one can simply query the database to extract almost any information that resides on the blockchain. This guide covers the basics of how to search, write, and visualize basic queries on Dune, so you can go from zero to blockchain analyst in no time. Even if you have never used SQL before, only a few basic examples can go a long way.

At its core, Dune analytics aggregates the raw data from the blockchain into SQL databases that can be easily queried. For example, there is a table to query all Ethereum transaction, nicely separated into columns. Columns cover the sender, the receiver, the amount, etc.

Example query of 5 Ethereum transactions (source)

All this information is available for free. The free tier (requires opening an account) covers:

• Searching queries
• Writing new queries
• Creating visualizations and dashboards
• ethereum.transactions: All transactions on Ethereum
• ethereum.logs: Logs of Ethreum events emitted by contracts (such as Transfer)
• erc20.ERC20_evt_Transfer: All the transfer events emitted when sending tokens
• prices.layer1_usd : A price table for ETH and many other popular tokens, on a per-minute resolution
• date_trunc(‘day’, block_time): We do not need to select all the columns in the table, but only the ones we need. In this case, the block time and the value of ETH sent. block_time is in Unix timestamp format, but we are only interested in getting the “day” portion of it, so we truncate the rest of the data
• as “Date”: Gives the column an alias. This is not necessary but makes results easier to read, and graphs automatically have better labels.
• sum(value/1e18): Since we are summarizing all the ETH sent, we use the SUM function to aggregate the data. Since ETH has 18 decimals of precision, we divide the number by 1e18, got get values in ETH and not in Wei
• where block_time > now() — interval ’10 days’: Only look at block times of the past 10 days. This will also make the query run much faster
• group by 1 order by 1: 1 here is the first column we chose (date_trunc). We group the results by date and order them by date. Since we are grouping data per day, we must use an aggregating function for all other columns we select. In this case, we use SUM but we could have also used MAX, MIN, AVG, or any other aggerating function, depending on our needs.
• with txs as: Create a new table called txs from the following data
• from ethereum.”transactions” e: Take data from the table ethereum.transactions, and alias it the table as “e”
• join prices.”layer1_usd” p: Join the table with the table for prices and alias it as p. This join operation will result in a table with a column from both tables combined
• on p.minute = date_trunc(‘minute’, e.block_time): A join operation requires you to specify which column you want to join on. In this case, prices are only registered every minute, so we want to join the data with the minute the block was created. This will result in an entry for each transaction, but with the additional data from the prices table
• where block_time > now() — interval ’10 days’: As before, only take data of the past 10 days
• and symbol = ‘ETH’: The prices table has prices for many tokens, we are only interested in the price of ETH
• select date_trunc(‘day’, ... as “Value” from txs: Finally, we run the same query as before, but we multiply the value in ETH by the price. We also take the data from our txs table.
• ethereum.transactions: All transactions on Ethereum
• ethereum.logs: Logs of Ethreum events emitted by contracts (such as Transfer)
• erc20.ERC20_evt_Transfer: All the transfer events emitted when sending tokens
• prices.usd: A price table for WETH and many other popular tokens, on a per-minute resolution
• dex.trades: All DEX trades
• Popular Dune dashboards
• Dune Analytics docs

Thanks to Alex Manuskin for writing this post! You can find him here:

Dune: @ksunama

Twitter: @amanusk_

Github: @amanusk

Original Medium post here.

---

Header photo by Danielle MacInnes on Unsplash