# A beginner’s guide to Dune Analytics

By [ChainInsight](https://paragraph.com/@chaininsight) · 2022-06-17

---

ccurate data-driven insights are hard to come by in the world of tokens. Trends change over time, and market data is highly dynamic, largely due to the volatility of cryptocurrencies and the lack of easily accessible and reliable market information.

With this in mind, Dune Analytics, is looking to fill this gap by providing fast, easy and free access to encrypted data. Based on my own experience and data collection, in this guide, I'll explain to you what Dune Analytics is and how to use it simply from a variety of perspectives.

Please consider following me on Twitter @ChainInsight\_ I will keep digging and sharing.

In this guide, you will learn:

*   Part 1: **What is Dune**
    
*   Part 2: **How does Dune work**
    
*   Part 3: **How to use Dune**
    

* * *

### What is Dune？

Dune Analytics is a powerful blockchain analytics platform that can query, extract, and visualize massive amounts of Ethereum data. It is a web-based platform where information from blocks is parsed and populated into Dune's database after a delay of a few minutes. Instead of writing a dedicated script, users can query almost any information on the blockchain from a pre-populated database using simple SQL queries.

Dune Analytics was founded with the express purpose of making Web 3.0 data available to everyone, while allowing anyone to publish and access crypto trends in real-time. And, it is a community-based open source data provider that is freely available, so anyone can access and publish data without registration or payment. Of course, Dune also has a paid plan. For users with higher needs, you can choose a paid plan to enjoy more services.

The platform currently supports data from Ethereum — including second-layer solutions such as Polygon, Optimism, Binance Smart Chain, and Gnosis Chain. In addition, Dune recently released the V2 engine, which improves the performance by nearly 10 times, and integrates different databases together, making it more convenient for users to make comparison queries.

* * *

### How does Dune work?

In essence, Dune Analytics records and integrates all raw blockchain data into a SQL database, which can be easily queried by all users. For example, you can easily query all the transfer information of Ethereum in the Dune database. These data have been well sorted by items such as sending address, receiving address, and quantity. In order to give you a more intuitive display, we give an example, the purpose of this query is to query the latest 5 transaction information in Ethereum.

![Query the latest 5 transaction of Ethereum](https://storage.googleapis.com/papyrus_images/da936aa37b4d713d6f826694051df5f1bce4b780a51e662429b39ec420c0b070.png)

Query the latest 5 transaction of Ethereum

All this information is provided free of charge. The free tier (account opening required) includes:

(1) Search query

(2) Write a new query or dashboard

(3) Create visualizations and dashboards

(4) Execute 3 queries at the same time

In contrast, paid packages include:

(1) Make the query or dashboard visible only to yourself

(2) Export the data as a csv file

(3) No need to wait in line

(4) Execute multiple queries at the same time

(5) Remove watermark

(6) Execute 6 queries at the same time

* * *

### How to use Dune？

Next, we will give you a brief introduction to how to use Dune. After entering the Dune website and registering an account, the home page will look like this:

![Sign in to the Dune Analytics app](https://storage.googleapis.com/papyrus_images/cc4915cabef0808364badfafa1041b139e430b168819d8e5016f0bfed0bb09b6.png)

Sign in to the Dune Analytics app

The first screen you see is a list of your favorite dashboards. A dashboard is a collection of queries and charts that other users typically create around a specific topic. There are a large number of dashboards to query, covering mainstream DeFi projects, DEX transaction volume, gas usage of Ethereum transactions, and more. Users can also search for the project information they need on the right, and can sort by popularity, collections, and release date. Dashboard title, publisher, age, and star rating are displayed for each result. Users can choose the title that is most relevant to their interests, and it's worth noting that the more stars the dashboard has, the more popular it is. In general, dashboards with the most stars tend to be the most reliable.

![Query Dashboard](https://storage.googleapis.com/papyrus_images/93b542ba9d2ef9046c7b3cee9afbec2342ede2479ca0a9316980e216178f121a.png)

Query Dashboard

**Dune Dashboard**

For the searched dashboards, you can click the dashboard to view. Dune does not store any blockchain state data. It only shows the results of the Structured Query Language query that was requested when the dunes were last visited. For example, click on the Dex volume dashboard by @hagaetc (one of Dune's legends), here we can see various data pulled from the Dune database, displayed as aggregate data and graphs.

![view the dashboard](https://storage.googleapis.com/papyrus_images/2b8ad071a1ede9ffd805319efe1da343de87ac7f432e0a7f87039d8a4c362055.png)

view the dashboard

Users can get more information by hovering over the graph. All queries are automatically updated as the user views them. It's worth noting that the refresh may take some time, so data may be out of date from a few minutes to a few hours.

Each dashboard contains a set of queries, and each chart can be selected, viewed and edited. The chart can be enlarged, full screen, or selected in any part. Select Fork to copy the entire dataset into your own workspace, where you can manipulate, modify, create new charts, and save. And Query allows you to see the implementation code of this part of the dataset.

![Dex by volume](https://storage.googleapis.com/papyrus_images/d99e1d4fc4509bf3c8644f902829604b493842e1d1b8ad4e97a9fe14310d38a5.png)

Dex by volume

Here, the user can see two main screen elements: the query (top black box) and the output graph (bottom). No matter which block or chart is clicked, you can see how the user created the chart.

**Write queries and create dashboards**

Next we go on with how to edit and create queries.

**Search query**

Just like the search dashboard, users can select a query list from the top bar to search for a query.

![Search query](https://storage.googleapis.com/papyrus_images/20b554879d5fffe3864b9b93f179368b63cb6da23b9fbd1ba3fda0bd11fb6f08.png)

Search query

Not all queries are added to the dashboard, so there are more queries to filter. Once you find a query of interest, you can select, edit, or fork it, just like any query in the dashboard.

Dashboards are usually part of a query that the author chooses to highlight. When looking for information about a specific item, it's best to start with the dashboard, and if you can't find what you're looking for, you'll have to search through the query list.

**Wirte query**

Dashboards and queries from other Dune users are a good place to start when looking for information on a specific project, but sometimes existing queries aren't enough to answer the question you're researching. At this time, you need to write your own query language to build the query. Fortunately, Dune uses the standard PostgreSQL query language, which makes it easy to do some basic queries even for novice users who have not used SQL.

First, and most useful to get started with, are dashboards and queries written by others. As mentioned, all public queries can be forked, or you can simply copy the code from someone else. Making minor changes to the query that meets most of your needs can be very useful to help you solve your problem. Here, we'll write some basic queries that hopefully will help you a little bit starting from scratch or give you some inspiration.

To create a new query, the user can create it by clicking New Query. The new query page displays the SQL editor in the middle of the page and a list of data tables on the left. A drop-down menu above the data table allows the user to choose which blockchain to pull data from.

![create new query](https://storage.googleapis.com/papyrus_images/f20cfcde90a2cb6e094f7777ce0414e22b1e7768e652a4b57d008dc292abecaa.png)

create new query

First, the database must be selected for the query (Ethereum, Gnosis Chain, Polygon, Optimism OBM 1.0, Optimism OVM 2.0, BNB Smart Chain, and Dune Engine V2).

![choose the database](https://storage.googleapis.com/papyrus_images/54c59c0a5ef6bf94e19cb319e9b85c7142f4a1a57c52dc6a6b6f2e65cbcd2eb3.png)

choose the database

The data tables are arranged alphabetically by protocol, with each table representing a smart contract call or event. Most queries use the event table instead of the call table. Finding the correct table for the query requires a deep understanding of the protocol being analyzed. (Recommendation: read the protocol documentation, analyze Etherscan transactions, and understand which smart contracts contain relevant transactions.)

Examples of some frequently used tables:

·ethereum.transactions: All transactions on Ethereum

·ethereum.logs: Logs of Ethereum events triggered by the contract (eg: Transfer)

·erc20.ERC20\_evt\_Transfer: All transfer events triggered when the token is sent

·prices.layer1\_usd: price list in minutes for ETH and many other popular tokens

**Create visualization**

Every query begins with a research question. The idea of solving the problem is usually: the first step is to clearly define what we want to know; the second step is to quantify the problem, that is, to clarify the query indicators; the third step is to write a statement to obtain data; the fourth step is to visualize the data output dash board.

For example, want to know the total value of ETH sent each day for the past 10 days. First, determine the research direction, you can get data in a table containing all Ethereum transactions; second, search for transaction in the search field, and a related list will be displayed, from which ethereum.transactions can be selected.

![choose table “transactions”](https://storage.googleapis.com/papyrus_images/b57b6e951dc6cf7d7aaf1c887802e741f9e455cc67935231520dbbda839d0d8f.png)

choose table “transactions”

Clicking on a table in the table list will display all available columns in that table. In this case, we use the ethereum.transactions table with columns hash, index, gas\_price, etc.

![column names contained in table “transactions”](https://storage.googleapis.com/papyrus_images/44f9f753b235a3b1ae653c9ee2f3de82b0c65c5905e13f0066b92449790ccc34.png)

column names contained in table “transactions”

Click the double arrow to copy the name into the query field. Click the double arrow next to the table or column name to paste the name in the query section. This helps avoid manual copy-paste and typing errors. The query for this case is as follows:

![the query](https://storage.googleapis.com/papyrus_images/6002c8cd38ab359a54c5dfba019760f43c0e501ecd450b9c1cf9cb933fef7e1c.png)

the query

To explain:

date\_trunc('day', block\_time): We don't need to select all the columns in the table, but only the columns we need. In this case, the columns block\_time and value are required. block\_time is in Unix timestamp format, but we are only interested in getting the day part of it, so we truncate the rest of the data.

as Date: Specify an alias for the column. This is not required, but makes the results easier to read and the graphs automatically have better labels.

sum(value/1e18): Since we aggregate all ETH sent, we aggregate the data using the SUM function. Since ETH has 18 decimal places of precision, we divide the number by 1e18, and we get the value in ETH instead of Wei.

where block\_time > now() — interval '10 days': only view block times for the past 10 days. This will also make the query run faster.

group by 1 order by 1 desc: 1 is the first column we select (date\_trunc). We group the results by date and sort by date. Since we are grouping the data by day, all other selected columns need to be aggregated. The function sum is used here, you can also use max, min, avg or any other aggregate function as needed. desc means descending sort.

Execute a query to get a list of dates and the sum of ether transferred for those days. The query results are as follows:

![query results](https://storage.googleapis.com/papyrus_images/ed36ab8d117610b0e95bbf57bf0de99b036cdd87a04d0c67e87e66c428c3e4a1.png)

query results

The next step is to visualize the results obtained. Select New Visualization to go to the visualization menu.

![add visualization](https://storage.googleapis.com/papyrus_images/75f5d92b8da59a176a032baf718ab4640460cee2d291ec8a73e2b183cbd8059d.png)

add visualization

Click on the New Visualization section to go to the Dune visualization menu, where there are several visualization types to choose from. The most useful is to use Chart to draw simple charts, Counter to display single data, Table to display pivot tables and so on. In this case, to draw a trend graph of the total value of ETH sent every day in the past 10 days, you can select Bar Chart, click Add visualization, and select the x-axis and y-axis accordingly to get the visualization of the query results.

![the new visualization](https://storage.googleapis.com/papyrus_images/20beb1b85fb61b6f95ed16efe71f89aa93c18d0dcaede9f84e6a7e4d760c0b41.png)

the new visualization

This results in a basic diagram. There are many more possibilities to try. Such as changing chart styles, colors, labels, etc. Finally, save the chart to add to the query results. Multiple visualizations can be created for each query.

**Create Dashboard**

Now that we have charts, we can aggregate them into dashboards. Click New Dashboard on the home page to give the dashboard an informative name.

![create new dashboard](https://storage.googleapis.com/papyrus_images/2da3fb31b2f72bc81973c08c4fdbaef27da87c52bb0cb6daedd4305a385846b3.png)

create new dashboard

Widgets can also be added using Add Widget in the Dashboard panel or Add to Dashboard in each visualization in each query.

![add widgets](https://storage.googleapis.com/papyrus_images/b9df4221092f11e0ade0d1ba6785d3f136e90f0f2a3fc55bfc84851bf198f9df.png)

add widgets

**Limitations of Dune**

Although Dune is a super powerful tool, there are still some bugs and limitations worth mentioning. First, currently only events such as transactions and transfers can be queried. The state of the blockchain cannot be queried at a particular block. For example, to know what a particular address's balance is in a block, you would need to create a query that aggregates all transfers in and out of that address. Answering the question "What is the total supply of Ethereum" is currently a bit tricky.

While the platform can help you debug a bad query most of the time, sometimes a query hangs until it times out. If the query takes an unreasonable amount of time, you may have to try saving it and reloading the page. These errors may be eliminated in the future.

Queries have a 40 minute limit until they time out. Querying large amounts of data and multiple joins can hit the limit. Consider filtering queries (eg, block time or block number) whenever possible, as these operations can greatly increase query time.

Finally, as mentioned earlier, a free user can only limit 3 queries at a time. If you want to update a dashboard with multiple charts, this may be limited. At this time, users in need can choose to subscribe to a paid plan to enjoy More services.

**Some tips about sql**

PostgreSQL has very strict requirements on SQL syntax, which is closer to the SQL standard and stricter than mysql. The basic query statements and corresponding explanations are introduced in the previous query cases. Here are some usage examples of system functions and time functions, which may give you some inspiration:

![](https://storage.googleapis.com/papyrus_images/93caf85ae1198026a9c1a1b7d89f6481039a03ab17c2b1673e61aba25dd2395a.png)

![](https://storage.googleapis.com/papyrus_images/318faabc07436bd11fca5e09bc1ab4f371048edc1feb0f7e5979ce3a4204894d.png)

![](https://storage.googleapis.com/papyrus_images/154e768a4133cba5b7ca11197410675b8d126b402b74c1086086ba305cdabc70.png)

**Conclusions**

Mastering PostgreSQL, understanding the database structure, and being familiar with the interpretation of each data field will help you analyze and solve more complex problems and build dashboards. An effective way to learn and dig deeper is to look at what other sharers have already built: fork the query and modify it to solve the specific problem you're researching. (It is usually a good practice to thank the original authors when using their work). For those dashboards or queries that align with your research direction, you can use an asterisk mark for frequent access and reference.

Blockchain data is open and free, and Dune Analytics provides more convenience for users who study data on this basis. The maintenance of the community depends on everyone's sharing. Data is fluid, and Dune has truly achieved this. If you are interested, don't hesitate to create your first query!

Since we have been working on Dune research for some time, we have studied and launched Optimism dashboard, $OP dashboard, etc. The follow-up research direction is mainly the research of some newly issued tokens. If you are interested in our research, Welcome to our homepage!

[https://dune.com/chaininsight](https://dune.com/chaininsight)

* * *

Referrences:

\[1\] [https://www.duneanalytics.com/](https://www.duneanalytics.com/)

\[2\] [https://dune.com/chaininsight](https://dune.com/chaininsight) 

\[3\] [https://qiita.com/shooter/items/3b66fc6400bc49854ffe#top-10-token-holders-%E6%9F%A5%E8%AF%A2%E4%BB%A3%E5%B8%81%E7%9A%84%E5%88%86%E9%85%8D%E6%83%85%E5%86%B5](https://qiita.com/shooter/items/3b66fc6400bc49854ffe#top-10-token-holders-%E6%9F%A5%E8%AF%A2%E4%BB%A3%E5%B8%81%E7%9A%84%E5%88%86%E9%85%8D%E6%83%85%E5%86%B5)

\[4\] [https://mirror.xyz/phillan.eth/17VAXsMPpwJg4OQNBHKTYAQTWfJMwFuXZQDAxPStf0o](https://mirror.xyz/phillan.eth/17VAXsMPpwJg4OQNBHKTYAQTWfJMwFuXZQDAxPStf0o)

\[5\] [https://collectiveshift.io/member-videos/dune-analytics-guide/](https://collectiveshift.io/member-videos/dune-analytics-guide/)

\[6\] [https://beincrypto.com/learn/dune-analytics/#h-how-do-you-use-dune-analytics](https://beincrypto.com/learn/dune-analytics/#h-how-do-you-use-dune-analytics)

\[7\] [https://www.cnbctv18.com/cryptocurrency/what-is-dune-analytics-and-how-can-it-help-investors-13215012.htm](https://www.cnbctv18.com/cryptocurrency/what-is-dune-analytics-and-how-can-it-help-investors-13215012.htm)

\[8\] [https://cloud.tencent.com/developer/article/1754684](https://cloud.tencent.com/developer/article/1754684)

---

*Originally published on [ChainInsight](https://paragraph.com/@chaininsight/a-beginner-s-guide-to-dune-analytics)*