Hey everyone, hope everything is going well on your side! 😃

Today we’ll start introducing a pretty powerful analytical tool, called Dune Analytics largely used all throughout the space and enabling power users to freely and easily access on-chain data through simple SQL queries in order to create dedicated analytics dashboard supporting market research.

So to do that, we’ll create the below dashboard analysing a set of key metrics of the Chainlink ETH/USD data feed in order to learn how to query data through Dune analytics and also present those data using the different types of modules offered by the platform (counters, graph, tables …)

Alright so let’s get right into it, first if you haven’t already created one you’ll need to create a Dune analytics account using the following link.

Once you’re set you should be met with the following homepage upon connecting to the platform.

From there click on “New query” in order to get redirected toward the query builder.

There as shown on the previous captcha we are going to be met with 3 main components:

Component 1 — The query section where we are going to write our SQL queries

Component 2 — The Query result section where we’ll be able to visualize and fine-tune the visual rendering of our queries (tables, graphs, …)

Component 3 — The table section listing all the available Dune Analytics tables per integrated network.

Alright so let’s start building our first component here and create our SQL query for the number of requests made to the Chainlink ETH/USD price feed over the last hour. First we need to get the address of the Chainlink Price feed contract on the Ethereum mainnet that you can find here.

Chainlink ETH/USD address: 0x5f4ec3df9cbd43714fe2740f5e3616155c5b8419

Then from there,we can start building our query.

So first we need to extract all the requests which interacted with the Chainlink contract over the past hour. So to do that, we’ll use the ethereum.traces database allowing us among other things to extract the address of the requesters and use the COUNT() SQL function in order to obtain the overall number of requests per requester.

From there we need to calculate the overall sum of total requests over the past hour. So to do that we’ll need to fine tune a litle bit our code and encapsulate our previous code inside a table called here table1 in order to be able to use a second table calling table1 and be able to apply the SUM() function over our request_nb column to obtain our total request number.

Alright so now that we have our value, let’s create a new visualization in order to get that counter view that we want. So first go ahead and save your new query. Then once it’s done click on “New visualization” and select Counter type before clicking on Add Visualization.

From there feel free to use the names and style that you want or if you want to follow strictly what I did enter the parameters showned below.

When you’re ready, click on add to dashboard and click on the add button linked to the dashboard your willing to use.

And here it is ! If you go back to your dashboard you’ll see that we created our first live widget, on our dashboard 😃

Alright so now reiterating the same process we could manually create two new queries and go through all our previous steps to create the daily and weekly request counters.

However, it would be cumbersome and Dune Analytics allows us to create similar queries in a much more clever way so let’s just use it 😉

So first, let’s get back to our previous query and click on the fork button on the upper right part of the screen.

As you can see, we’ll get automatically redirected to a new copy or fork of our previous query enabling us to use right off the shelf our previous work.

So from there first, we’ll save this new query as our daily counter and change accordingly our time specification in our SQL queries before adding this counter to our new dashboard.

And then reiterating the same process, we easily create our weekly counter as follows.

Alright so now let’s move on to create our first table, showcasing the latest ETH/USD price feed requests. So here we want to be able to show 5 key informations:

• Time of the request

• Address requesting the ETH/USD price

• The gas limit used

• The actual gas price paid for the request

• The transaction hash

So first, let’s fork our initial query. From there as you can see by looking at the ethereum.traces dataset details, we already have at our disposal all those informations.

As such, the only thing that we need to do here to obtain our table, is to once again fork our initial query and fine tune it in order to extract all the needed metrics.

From there , as done previously we we need to save this new query and create a new visualisation in order to incorporate our table inside our dashboard. The only difference here being that we’ll go with the table visualisation.

From there again, feel free to use any style or title you want and remember to add this new element to the dashboard by clicking on the add dashboard button.

Alright so now moving on to our other counter, corresponding to the daily gas costs, here it is again pretty straightforward so try it for yourself before reading the rest in order to try it out for yourself and see if you start to get a hang of it.

Solution

Here as previously we’ll fork our initial query, however this time we don’t want the sum of all transactions but the average gas cost over a day period as such we’ll use here the AVG() function as follows:

Observation: Here given the inner limitation of Dune analytics we cannot only truncate our set of data in order to obtain solely the current date data through using CURDATE() so in order to tackle this issue we’ll use here the following work around consisting to order our extracted dataset by descending time order and then only keeping the first value using LIMIT.

From there as per usual use the counter visualisation and add it to your dashboard.

Alright so now let’s get to our graph visualisation, representing the evolution of the average gas cost over the past 14 days. To do so, let’s start by first fork our previous gas cost query. From there just change the interval day from 1 to 14 and here you go, you end up with the following table summarising the average ETH/USD query gas cost for each day over the past two weeks.

From there click on new visualisation and choose Area chart.

And here it is! You have a brand new live graph chart exhibiting the evolution of the gas cost over the past 14 days.

Alright, so now let’s create our last component and create our table showcasing the 10 main biggest ETH/USD price feed requester for the current day. So first, let’s fork our initial query.

The first thing that we need to do here is to work around the little limitation of Dune Analytics preventing us to only have value from the current day without yesterday’s value. Indeed here, if we only use the COUNT() function and sort our table with by the number of requests we’ll end up with the following result which is not that what we want.

As such to avoid this situation, a quick work around is to couple our previous request number ordering with another ordering applied on the time column as follows:

Alright so from there, even though we could stop here, it appears interesting to change the requester address with their real name whenever possible. As such, to do so, let’s head over to etherscan. There, using the contract address from our table let’s search the owner of the \xcf7fe2e614f568989869f4aade060f4eb8a105be contract.

Note: It is mportant to be aware that as per convention Dune analytics replaces the initial 0 of each Ethereum address by “\” as such when using Etherscan we have to be cautious to reformat the address into 0xcf7fe2e614f568989869f4aade060f4eb8a105be.

Furthermore, finding the owner of the contract might be a bit tricky at times and might even necessitate in some instances to either browse a bit around Etherscan or Google to find the contract owner.

However, in our particular case here, it appears as you can see pretty straightforward as shows the below Etherescan where we can easily spot that this contract corresponds to the ENS domain protocol.

Additionally, if you want to double-check on that point, one way to do so is to extract in Dune analytics the latest transaction hash, select those originating from that particular contract address and then go check the internal transaction of this transaction in etherscan to see if this contract is indeed used in the suspected context.

Example for the 0xcf7fe2e614f568989869f4aade060f4eb8a105be ENS domain contract:

Used transaction hash: 0x12cb364247f1fd1c93bbfb255c238ed4e9ea039482248a3ae5866d51a92fd130

Alright so from there once we have all our corresponding names for each address let’s integrate them in our table. To do so, we’ll have to refine a bit our code and as before incorporate our current existing code in a table that we'll call table1 in order to then be bale to call it in through another table and apply a set of transformation on our requester_address column using the WHEN argument allowing us to replace our current Ethereum address by its corresponding human readable name.

And from there you should be able to obtain a table with much more understandable names

Observation: In some instance, it is just not possible to find the contract adress owner so don’t sweat it and just put the reformated contract address for everyone to see in your table. Furthermore, given the used SQL function if a new address appears the following day in your table, the request origin will show a blank so I’ll let you fine tune that part as a small exercise if you want to have a better polished dashboard version.

Alright and after all that by just adding this new component you should have on your dashboard all our needed components. So from there just go ahead and click on the edit button located at the top right of your dashboard in order to be able to move things around and reformat the visual design of your dashboard as you prefer.

Note: If you want to create custom widget as the one showing the CyberGen Lab logo just just click on Add text widget and use the set of available markdown.

And that’s it ! Congratulations you made your first Dune Analytics dashboard 🥳🥳

So as always don’t hesitate to build upon that, create your own dashboards on other projects and have fun with the plaftorm 😃

Until next time, see y’all 🖖

Resources

Link to Chainlink ETH/USD price feed dashboard (Ethereum mainnet):

Link to Chainlink ETH/USD price feed dashboard (Polygon mainnet):