Dune-Visualizations
The queries and visualizations for this post can be found here → https://dune.xyz/queries/114570
Now that we have covered some basic queries I am ready to start diving into the visualizations.
Dune offers five styles of charts, a counter, and raw data table that can be built on top of your query results.
From a single query you can create multiple visualizations, and these visualizations can then be imported into a dashboard.
The charting is actually fairly easy, given some of the more complex visualizations I have seen on Dune.
Let’s run through a line chart using the query from the “prices” table. Oh the famous price chart!
Select
date_trunc(‘day’,”minute”) as “day”,
max(“price”) as “price”
from prices.”usd”
where symbol = ‘WETH’
AND date_trunc(‘day’,”minute”) >= ‘08–09–2021’
GROUP BY date_trunc(‘day’,”minute”)
ORDER BY date_trunc(‘day’,”minute”) DESC
After you run the query you then select “New visualization”button next to the Query Results button. The first prompt asks you to select the Chart Type…Line Chart, and then select “Add visualization”.
The area where your query results had previously appeared will revert back to the Loading… message, and maybe a chart will appear(mine did not).
At first, there was an error the X-axis was missing, so upon further study below the error you will find all the different chart options, where you can first and foremost tell Dune which column is your X-Column, and which is your Y-Column.
For our example, the X-Column is “day” and the Y-Column is “price”. Upon selecting these the chart will immediately appear above. Pretty easy right?
Walking through some of the options you can mess with;
Add data labels and format them. Set the label format to # to get rid of the decimals, or take it a step further and set to #,##0 to add commas to your values.
You can adjust the axis labels, calling out the Y-Column as “USD$”, and change the chart title to “ETH price USD”.
Near the bottom you can change the color of the “price” line (I’m an Orange/Gray guy), and also change the line label to “ETH price”.
The Column chart is very similar in setup to the Line chart as expected.
To test out the other charts(Scatter, Pie, and Area) we probably need a different data set, and since I can only have one query per set of visualizations I will save those for another post.
Moving onto the Counter, we get a completely new set of options. The Counter is not so much a counter as it is a “KPI box”. You select the column, and the Row Number and it will display the result.
For our example, we might want to show the the “price” column and select Row 15 to display the current price.
The problem with this method is the 15th row would not be dynamic, and tomorrow would not displays “todays price”.
You would need to add a new column labeled today’s price and display that column if you wanted and get a dynamic result.
This is where things might get tricky as you try and consolidate multiple visualizations around a single result.
To solve this, I used a CTE for “todays_price”, and then a CROSS JOIN to my original query.
WITH “todays_price” as(
SELECT max(“price”) as “todays_price”
from prices.”usd”
where symbol = ‘WETH’
AND date_trunc(‘day’,”minute”) = date_trunc(‘day’,NOW())
)
Select
date_trunc(‘day’,”minute”) as “day”,
max(“price”) as “price”,
“todays_price”
from prices.”usd”
CROSS JOIN “todays_price”
where symbol = ‘WETH’
AND date_trunc(‘day’,”minute”) >= ‘08–09–2021’
GROUP BY date_trunc(‘day’,”minute”),”todays_price”
ORDER BY date_trunc(‘day’,”minute”) DESC
Now I get a field called “todays_price” which is the same for every row, and I can simply flip my counter over to that column and set the value to row 1. This would now make this counter dynamic regardless of how many days I was reporting the price for.
Moving on to a Table is agan very simple. You can label the columns, adjust the number format like we did for our charts, and set the text alignment.
One thing here is that I do not see an option to HIDE a column, which puts me in a weird conundrum with “todays_price”. Again…another challenge of multiple visualizations from a single query.
I think Dune really did a great job of consolidating the options on these charts to give you just enough flexibility, while keeping it simple for users.
I definitely look forward to uncovering some tricks on how to consolidate multiple visualizations into a single query. That will become very important as we move onto the dashboards.
