Blockchain and crypto researcher.
Blockchain and crypto researcher.
Subscribe to 0xPhillan
Subscribe to 0xPhillan
Share Dialog
Share Dialog
<100 subscribers
<100 subscribers
Learn how to use Dune Analytics, for complete Dune and SQL beginners. Please consider following me on Twitter @0xPhillan
Dune is probably the strongest blockchain data analytics tool currently available to the public, and what's best is: it's free! With Dune you get near real-time access to blockchain data through a public database that you can query through Dune's website using SQL.
That's a lot of power.
Dune decodes blockchain data before adding it to their database, meaning that you don't have to figure out bytecode communication on your own. Instead, you use Dune's dataset explorer to browse datasets, specific smart contracts, events, or calls!
With Dune's recent announcement of their V2 Engine, which increases performance by 10x, it's about time you learn how to use Dune.
In this guide, you will learn:
Part 1: The Dune interface
Part 2: Build your own queries and charts with SQL – starting from the very basics
Part 3: Organizing it all into a dashboard
In this step-by-step guide we will build the queries of below dashboard for the Pooly NFT series:
Let's get started!
Part 1: Overview of Dune and Functionality
Dashboards
Queries
Forking
Query Editor
Dataset Explorer & Data Categories
Saving Your Forked Query
Part 2: Building Your First Query
Deciding Which Queries to Build
Finding the Right Information
Getting Ready to Build Your First Query in Dune
Query 1: Funds Raised in ETH
Query 2: Funds raised in USD
Query 2a: Funds raised in USD at Current ETH Value
Query 2b: Funds raised in USD at ETH Value at Time of Purchase
Query 3: Total Number of Supporters
Query 4a: Leaderboard using the erc721. Abstraction
Query 4b: Leaderboard using the poolysupporter. Decoded Table
Query 5: Max Supply and Remaining Supply of Each of the NFT sets
Query 6: Timeseries Chart of ETH Raised Over Time
Part 3: Cleaning up the Dashboard
Closing
When you first open the Dune website at Dune.com, you are greeted with below window. This window has a view changer at the top that lets you cycle through dashboards, queries and wizards, and then the detailed view area in which you can see the list of dashboards, queries and wizards (users) on the left as well as some search-related settings on the right.
Dashboards are a collection of queries that are arranged as a series of charts, counters, and other information to give the user context about a specific area of interest. Below, I have opened an Ethereum dashboard by the legendary @hildobby. Here we can see all sorts of data that is being pulled from Dune’s database, shown as sums and timeseries charts.
In Dune, every dashboard is public. That means everything you build or other people build can be viewed and forked (i.e., copied) by anybody! This severely decreases dashboard creation time and lets you learn from other users' queries.
If you recall, I mentioned that dashboards are a collection of queries. If you click on the title of any one of the dashboard elements, you'll be taken to that chart's SQL query:
Here in Here we can see two primary on-screen elements: the query (top; black box) and the output chart (bottom). That’s right: no matter which block or chart you click on, you can see how the user created that chart!
If you want to save an entire dashboard or only the chart’s query to your own account, you can click “Fork” on the top right, and everything on the forked screen will be duplicated to a new window, in which you can make edits before saving the views to your account.
Let's fork the Ethereum Price chart! Once you press "Fork" on a query, you'll be taken to the query editor with the previous code already copied in!
Let me introduce you to the various on-screen elements here:
Query location & name – name can be changed once you hit save!
Dataset explorer – search for a specific dataset
Query window – input your SQL queries here
Visualization selector – choose whether to see query results, the forked line chart or create a new visualization
Run – run the query that is in the query window
Results/Visualization – see query results or your visualization created with the query results
Save – save your (forked) query!
Let's take a closer look at the dataset explorer. There are six functional areas within the dataset explorer:
Chain selection
Dataset search
Browse raw blockchain data
Browse data of decoded contracts
Browse abstractions
Browse community-supplied data
In the dataset selection you can choose which chain you want to parse. Selecting "Dune Engine V2 (Beta)" let's you use Dune's latest enhancements which include multichain queries and 10x performance increases.
If you choose another chain, you the category selection (items 3-6 in previous image) will disappear and instead you'll see a list of contract calls and events that you can interact with.
In the search field you can input your search parameter, and Dune will search through all tables that include that keyword in some form.
Note: The Dune Engine V2 and the old search function return results in a different fashion. The old search returns a list of all results, while Dune Engine V2 returns a nested list of results. We will be using the V2 engine!
If you click into raw blockchain data, you can easily find queries for the various blockchains that Dune supports in a nested data structure where you can first pick your raw table, and from there pick the specific table columns you want to investigate further. Within each level of nesting you are also given the option to filter for specific search results you are looking for.
This is a very quick and handy way to get high level blockchain data.
In here you will find projects that have been decoded by Dune. Decoded projects are projects that the Dune team took apart, labelled, and put into tables so that users have an easy and standardized reference for certain data.
You will notice that again, the search results are nested. At the highest level, we have projects that you can search through, at a lower level you can filter specific smart contracts within that project, and finally we get to the various tables generated from that smart contract. If you click on any of the tables, you will see a list of columns, just like in with the raw blockchain data.
Abstractions can be thought of as custom tables that connect and combine various queries and pieces of data to form a unique table. Abstractions help users to query specific data they are looking for more easily, without the trouble of manually combining various pieces of data.
Generally, abstractions can be split into two main categories:
Sector abstractions: sector-specific data
Project abstractions project-specific data
From the abstractions sub-menu, we can see a list of abstractions with labels that specify whether the abstraction is sector-specific or project specific.
The community section can be thought of as an extension of the abstraction section, but with data aggregations being supplied by Dune community members.
You may wonder why there is only one entry in the community section ("flashbots") – well, that's because Dune Engine V2 was just released! Over time, we can expect to see more and more community-built datasets by trusted community members.
In below illustration you can see a summary of how data is summarized within Dune as of launch of Dune Engine V2: the four main data categories are raw blockchain data, decoded projects, abstractions, and community, which hold the data of various blockchains in table format that can hold various data types.
Let's go ahead and save this query first. After you hit save, a few things will happen. First, you will be asked to give your query a name.
Once you've chose a name, you will notice (1) the query location and name have been updated to your chosen name and (2) your query is running. That means Dune is fetching the latest data from their database, which is periodically updated with the latest data from various blockchains.
Once the query is finished running, you'll see your query results (3).
From here, if you click any of (1) "Query results", "Line Chart" or "New visualization", the (2) result/visualization box will update together with (3) settings for your selection that appear beneath it. Here you also have an "Add to dashboard" button, to quickly add your query results or visualization to new or existing dashboards – just like @hildobby's Ethereum dashboard from earlier!
If you click on (1) the circle in the top right and then on (2) “My queries”, you will open the queries list of your account.
The queries list includes all queries you’ve ever saved on your account. In below screenshot at the top we can see the latest query created:
Congratulations, you've forked and saved your first query with a visualization!
Forking is a Dune superpower, and it can help you easily and quickly create new queries by building on what other wizards (yes, you're a wizard now too!) have built before you. You can combine multiple forked queries to build your own dashboard!
Let's get our hands dirty and build a dashboard – a collection of queries and visualization – from scratch without forking. This will teach you where to find the right blockchain details to look for your specific project as well as teach you SQL basics.
The purpose of this section is to teach you:
How to find the right information you need for your specific project
Some basic SQL
But first, we need to decide what we want our dashboard to be about. Pooly NFTs by the Pool Together DeFi protocol are a great first step.
If we search for "Pooly" on Dune, sure enough we can find some Pooly NFT trackers created by the community.
We could click into one the Pooly dashboard created by @0xbills and click on "Fork" and work from there…
However, if we build it from scratch we get to learn how to be a blockchain detective and learn some SQL as well! So instead, we'll build our own from scratch.
First, let's decide what charts we want on our dashboard. Let's rebuild the views that Pooly built on their homepage! Taking a closer look at below two screenshots, we can see a few indicators based on on-chain data.
We can see:
Funds raised vs funding target denominated in ETH
Funds raised vs funding target denominated in USD
Total number of supporters (unique addresses that purchased a Pooly)
Leaderboard including addresses, number of NFTs purchased per address and total ETH spent in descending order
Max supply and remaining supply of each of the three NFT types
Awesome! But those are only a snapshot in time. Let's give ourselves another challenge as well:
Timeseries chart of ETH raised over time
As it stands now, we can't build the views in the same way as the Pooly website, but we can capture the same amount of data (and more!) to build our dashboard.
Before we get started on Dune, we need to find the right information. From the website we can tell that PoolTogether is selling three sets of NFTs:
Supporter – 1 of 9 random collectible for 0.1 ETH
Lawyer – Only one artwork for 1 ETH
Judge – Only one artwork for 75 ETH
Is Pooly selling all three NFTs through one contract, or through three different contracts?
Let's head to Etherscan and see if we can find a smart contract related to Pooly. Once you open Etherscan.io type "Pooly" to see if the owner of these smart contract registered them with Etherscan.
Indeed! There are three smart contracts, likely corresponding to each of the three NFT collections. Furthermore, we now know that each Pooly is an ERC721 token.
Open each of the three collections and copy the smart contract address by clicking on the copy icon that appears upon hovering over the address. At the bottom of the page we can see all recent transactions as well, which will be helpful for troubleshooting later.
We will need these smart contract addresses to pull the right data from Dune, and they form the foundation of all of our queries:
0.1 ETH Pooly Supporter:
1.0 ETH Pooly Lawyer:
75 ETH Pooly Judge:
First, navigate to dune.com and click "New Query" on the top right of the screen.
This will open the query editor and we can start working on our query!
First, in the top left switch from "7. Dune Engine V2 (Beta)" to "1. Ethereum". Pooly lives on Ethereum, thus we only need Ethereum data for this query. Plus, the tables in "1. Ethereum" are more mature than than Dune Engine V2, which only just entered beta.
For our first query, we will build a counter that shows funds raised denominated in ETH. To do this, copy below code into the query field in Dune and press "run" (or CTRL+Enter):
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
The above code is an SQL query that parses through Dune's database to fetch specific data that we requested. You can imagine Dune's database to be a collection of various tables, each containing specific information you may want to pull. With SQL, you…
specify what data you want (which column in a table)
whether you want to transform that data
from which table you want to get the data
whether you want to filter the data
To illustrate the above, let us run the above code piece by piece. Copy the below code into Dune's query editor and run it:
select * from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
You will get a big table with a lot of information:
Now let's walk through the SQL code:
What this code says is "select all columns from the transactions table within the ethereum category where the value in the to column is \x3545192b340F50d77403DC0A64cf2b32F03d00A9" or in plain English: show me a table with all smart contract interactions with the Pooly2 (1 ETH) smart contract.
You don't need to run a query to see the columns within a table. The data explorer let's you explore the various table headers through their nifty search function:
We could have removed line 3 entirely to remove the filter, however, this would have returned a massive table and the query would have taken a long time to complete. The more precise your queries, the faster they will run!
Because we only want to return funds raised, we don't need all columns. So let's adjust our code to only grab the "value" column:
select "value" from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
Now we only have one “value” column instead of the many we saw before:
You may notice, however, that the values seem to be quite large. That's because they are denominated in Wei and not ETH! To fix that, we can simply apply an arithmetic operator to the "value" column:
select "value"/1e18 from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
That looks better! 1e18 in SQL is the same as 10^18, and we are simply telling Dune to divide the number by 1,000,000,000,000,000,000 so that instead of Wei, we see the values denominated in ETH.
And since we only want the total value and not a list of value, we can wrap the "value"/1e18 in a SUM() statement:
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
Awesome, we can now see the total number of ETH spent on Pooly2! And since we want to get the total spent on all three Pooly NFT smart contracts, we need to add two more lines to include details about the other smart contracts:
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
The "or" command works in tandem with the "where" command and specifies that when filtering the values in the "to" column, that the row should be considered if the first value or the second value or the third value are found.
We now see that in total 773.7 ETH has been spent on all three Pooly contracts. Awesome! Let's go to the Pooly website to see if that's right:
On the Pooly Website we see 776.5 ETH - congrats on achieving the target! But, oh no, there's a 2.8 ETH difference! Well – that's nothing to worry about. Dune syncs blockchain data in regular intervals. And because the dataset they sync to their database is so massive, it takes some time. We can expect the data to be refreshed in the next hour or two.
Now that our query is complete, we need to set up a counter to display this on our dashboard later. Below the Query results box, click on new visualization, and in the drop down menu that appears click on "counter".
And finally, click on "Add visualization":
A counter will appear and if you scroll down, you'll see various settings show up. Simply adjust the settings to your liking.
Once done, click on (1) "Add to dashboard" and select (2) "New dashboard". Then (3) give your dashboard a name and (4) click "Save dashboard". Your new dashboard will appear in your dashboards list. From here click (5) "add" on the dashboard you wish to add your visualization to. Once added, the label will change from "add" to "added".
If you click on your dashboard's name in this sub-menu ("Pooly NFT by 0xPhillan"), you will be taken to a dashboard that has our tracker displayed.
Great!
We'll get back to editing our dashboard once we have completed setting up all queries.
There are two ways that we can approach this:
Use the current value of the funds in USD used to purchase the NFTs
Use the USD value of the funds at the time of purchase
If we look at the smart contracts on Etherscan, we can see that a large portion of the 776.5 ETH have already been moved out of the smart contracts, leaving 299.2 ETH in the Pooly NFT smart contracts as of time of writing.
If we look at the Pooly website screenshot from earlier 776.5 ETH is valued at $1,411,249 USD ($1,817 USD/ETH), hinting that possibly the Pooly smart contract owner is keeping the funds as ETH instead of trading for USD.
Ultimately, it's difficult to say which approach Pooly is taking, but both approaches to USD value are interesting:
The current value tells us what the funds are worth now
The value at time of purchase tells us the intended USD donation amount by purchasers
So… let's create both!
For this one, we'll use our previous code as a base and slot in some additional lines to get the current USD value.
First, fork the query we just created:
Then adjust your code to look as follows:
select SUM("value"/1e18) * (
SELECT "price" FROM prices.usd
WHERE "symbol" = 'WETH'
AND "minute" < now() - interval '1 hours'
ORDER BY "minute" DESC
LIMIT 1
)
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
You'll notice that we have added a multiplication operator * and a large code block right after our SUM("value"/1e18) command.
In Dune, you can highlight specific portions of your query and only run that portion by clicking "Run selection". Let's (1) highlight only the lines within the parentheses and (2) run that selection:
In the query results you'll see the latest USD price of WETH! Our addition here multiplies the latest price of WETH with the amount of ETH raised to give us the USD value.
Let's break down this code block:
From the prices.usd table select the "price" column
Filter the symbol column for "WETH"
Only look at time entries of the past 1 hour (this will significantly speed up the query)
Order in descending order (newest first)
Limit the query to one result (the first result, i.e., latest price)
To better understand this block of code, let's make some small adjustments to our query. (1) replace "price" with * (which returns all columns) and (2) select only the code from lines 2 to 5, then (3) run the selection:
In the query results you will see the full table consisting of five columns. First, let's check the contract address in Etherscan.io:
This smart contract controls the WETH asset on the Ethereum blockchain. Great! Now we know the source of the table from our previous screenshot that shows the WETH price in USD.
Let's divert our attention back to the previous table:
In here we have a column called "minute" which tracks the ETH to USD value every minute. Since we limited the query to "interval – '1 hours'", we only get the latest hour of data available. Since for our purposes we actually only need the latest data entry, limiting this query to the last hour speeds up the query significantly. You could also change this to '1 days', '3days' or '1 weeks' for example, to get more historical data.
What's important here is that the columns name is "minute", hence our query refers to the "minute" column, which is not to be mistaken for a time-related command.
Let's revert our code back to what we changed it to at the beginning of this section and run the query:
The result is the current USD of the ETH that has been transferred to the Pooly1, Pooly2 and Pooly3 smart contracts in exchange for Pooly NFTs.
For this we will be using a counter again, so scroll down and (1) click on the counter forked from our previous query, (2) adjust the data source and (3) change the labels.
Once finished, remember to save and add to our dashboard:
After it's added, it will look like the below screenshot. Don't worry, at the end of this guide we'll clean it up. For now, don't worry about the looks!
This query will be a little more complex, as we will have to query two tables and combine the results. Specifically, we will have to get the individual transactions and convert the ETH value of each transaction using the price of ETH at the time of the transaction.
Again, let's first fork our previous query in preparation for our next query:
From the forked code, we want to make the following:
with poolyTransactions as
(
select
block_time,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
sum(value_eth * price)
from poolyTransactions tx
left join
(select minute, price from prices.usd
where symbol = 'WETH' and minute > '2022-05-01')
as prices on date_trunc('minute', block_time) = minute
If we run this code here, we see that we receive a USD value of 1,4m USD. This is the result of
Let's break this code down into three sections:
Section 1
Here we build the first table that we will reference. What we do here is create an auxiliary table that we call "poolyTransactions" that will hold block_time and value_eth (value in wei divided by 10^18 to convert to ETH, which we give a custom name) from the ethereum.transactions table. For this table, we filter the three Pooly addresses that we know of.
Here a line by line explanation:
Line 1: with poolyTransaction as – define auxiliary table called "poolyTransaction" as having below properties
Line 3-11: select columns and filters to include on the ethereum.transcations table
Line 5: value/1e18 as value_eth – here we rename the column as "value_eth" so that we can reference it directly in section 2 instead of doing another calculation
Section 2
This is where we create the output table. You'll notice that we are building a table from poolyTransactions, the auxiliary table we created in section 1, but we are also referencing a column called "price" that we didn't define yet. Price is in fact only defined later in line 19! This is made possible, because we are joining the poolyTransactions with certain outputs from the prices.usd table in section 3. So in essence, we are creating a table using our auxiliary table poolyTransactions as well as the table we built from prices.usd in the next section.
Section 3
This is where we define a table that we want to join with another table. The "left join" keyword allows us to do this:
Line 18: left join – keyword used to indicate that we want to join our first table (left table) with another table (right table). That means, our first table defined in section 1 acts as the base table.
Line 19-20: here we define the table we want to create from prices.usd. In line 20 we limit the duration to '2022-05-01', because the Pooly smart contracts were only deployed in the month of May, so we can significantly speed up the table query if we limit it to a smaller timeframe instead of parsing data all the way to Ethereum's genesis block.
Line 21: as prices – this names our table in lines 19-20 "prices" for easier future reference
Line 22: on data_trunc('minute', block_time) = minute – this is the line that combines our auxiliary table (section 1) with the prices table (section 3 lines 19-20). What it says here is to take column "block_time" from our auxiliary table, and truncate it to minutes only, i.e., remove all other data that is not minutes (e.g. seconds, milliseconds, etc.). The prices.usd table is already truncated to minutes, so there is no further conversion needed here. Then the minute column in prices.usd is matched to the minute column in our auxiliary table, and the correct price is thus allocated from the prices.usd to the corresponding minute timestamp in poolyTransactions.
To better visualize the third section, I have reorganized the parts to make it easier to understand:
(1) We create the poolyTransactions table, then (2) we tell SQL to join it with another table, which (3) we define as minute and price columns from the prices.usd table. This prices.usd table we created is then (4) joined onto the left table poolyTransactions using the time in minutes as a mapping variable. To join tables, both tables must have entries which are exactly the same and if we truncate the block_time variable to minutes, we create matching minutes between both tables. By doing this, the (5) poolyTransactions table is updated to include the price column, with price values being matched to the respective dates.
From here, we simply query the joined poolyTransactions table and sum the result of multiplying the value_eth and price of ETH for each row.
Now add a counter, save and add to dashboard!
For our next query, we want to count the unique addresses that purchased a Pooly NFT (i.e., total number of supports). That means that even if an address purchased multiple Poolys across all three Pooly types, they should only be counted once.
For this, let's first open our very first query, fork it, and remember to save it too.
Here we simply change the first line:
select COUNT(DISTINCT "from") from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
The COUNT variable counts all transactions, and the DISTINCT keyword makes sure that every unique entry is only counted once. The result we get is 4660 unique supporters. If we compare that to the unique supporters on the Pooly website, we see they are very close:
This shows that our query is correct, as there is a little lag between Dune's database refreshes and the latest blockchain state.
Finally, change the counter visualization and add to dashboard again.
Next, let's build the leaderboard including addresses, number of NFTs purchased per address and total ETH spent in descending order.
Again, let's fork our previous query so that we avoid having to retype the filtered addresses. Remember to save this new query before proceeding.
Looking at the leaderboard, there are three pieces of information we need. First, the address of the purchaser, then the number of NFTs purchased, and finally, the amount of ETH spent on all NFTs purchased.
Here we are looking at the NFTs purchased not at the NFTs held. It is entirely possible that somebody purchased (minted) an NFT then moved it to a safe wallet or resold it at a later point. We are only interested in first-time purchases (mints).
We achieve this using below query:
with poolyTransactions as
(
select
"from",
hash,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
"from", nfts_purchased, value_eth
from poolyTransactions
left join
(Select evt_tx_hash, COUNT("tokenId") as nfts_purchased
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1)
as nfts
on evt_tx_hash = hash
ORDER BY 3 desc
You will notice that this heavily resembles the query in "Funds raised in USD at ETH value at time of purchase", and that's because we are using the same methodology: we first gather the transaction data in our poolyTransactions table, then we left a second table on it with a common mapping value.
Here, for the second table we use the erc721."ERC721_evt_Transfer" table, which is an abstraction that Dune maintains that tracks all NFT transfers on Ethereum. If we use the dataset explorer, type "erc721." And scroll to "ERC721_evt_Transfer" we can see everything that is included in that specific table. We can also highlight only the commands for the second table and see what the output would be:
Since we only want NFTs that were newly minted by the smart contract, we must specify the "from" address to be the null address. On Ethereum, all NFTs are minted from the null address. By counting the amount of "tokenId" for each transaction, we can count the total number of NFTs minted for each transaction.
You will also notice something peculiar about how the filters are defined. The first three filters are now enclosed in brackets, while the last filter is outside of the brackets.
The brackets determine the order in which the and/or filter commands are evaluated, just like when executing arithmetic commands in SQL. If we did not enclose the first three statements, the and condition would only apply to the last filter setting.
Since we want the from null address filter to be applied to all results of the previous filters, we need to add the parentheses.
Finally, since we are using the "COUNT" command, we need to specify what column to count these on (i.e., what variable to roll-up the counts to). For this we use the "group by" command to say that we want to group the count of "tokenId" to the first column in our table, which is "evt_tx_hash".
Earlier I mentioned that we need a common mapping value to map this second table to the transactions table. Here we use the transaction hash to map the number of NFTs purchased per transactions, to our poolyTransactions table where this time we also requested the transaction hash. So ultimately, we are mapping the transaction hash of our erc721."ERC721_evt_Transfer" table (which we named "nfts") to the transaction hashes of our poolyTransactions table, which only include transactions that were used to purchase poolys.
The output is a table that includes the purchasers address, the number of NFTs that purchased purchased in total, as well as the total value of ETH spent.
Finally, we tell Dune to "ORDER BY 3 desc", which means that the third column of our output table should be ordered in descending order:
Awesome! Our leaderboard is complete. Let's compare it against the leaderboard on the Pooly NFT website:
Not all figures tie, but from this list we can see that a few addresses, NFTs purchased and total ETH spent figures do tie. This is once again a sync timing issue between Dune and real-time blockchain data and nothing to worry about.
Remember to save your query and add it to the dashboard.
Instead of using the erc721."ERC721_evt_Transfer" table, we can also use the poolysupporter."PoolyNFT_call_mintNFT" decoded table that the Dune team put together.
with poolyTransactions as
(
select
"from",
hash,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
"from", nfts_purchased, value_eth
from poolyTransactions
left join
(Select call_tx_hash, "_numberOfTokens" as nfts_purchased
From poolysupporters."PoolyNFT_call_mintNFT"
where contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
as nfts
on call_tx_hash = hash
ORDER BY 3 desc
The methodology is the same as above, except that with this table we can directly return all transaction hashes that called the mintNFT function, instead of using the null address to determine which transactions from the erc721. "ERC721_evt_Transfer" table are mint transactions.
The poolysupporter dataset allows us to do more specific and detailed queries, as we can refer to specific contract calls.
Let's compare the results of our two tables to ensure nothing is amiss:
Won't you look at that, the outputs are identical. Great!
Remember to save your query and add it to the dashboard.
In the alternative version of query 4 we used the poolysupporter function. You may have seen that when you search for pooly in the dataset explorer, you also see a function called "PoolyNFT_call_maxNFT".
You may conclude that you can use this function call to directly retrieve the maximum minted NFTs.
Unfortunately, that is not possible: this function is a "read" function and as a result, there is no on-chain record when this function is called. See Etherscan below:
The maxNFT variable is set when the deployer contract deploys the Pooly Supporter smart contracts, but unfortunately at time of writing, the deployer smart contract has not been decoded, so we can't get the max mint numbers from on-chain data – at least not without significant effort.
Instead, we'll have to manually slot in the maxNFT figures for each smart contract:
with poolyContracts as
(
Select contract_address,
COUNT("tokenId") as nfts_purchased
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1
)
select
CASE contract_address
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 'Pooly_Supporter'
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 'Pooly_Lawyer'
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 'Pooly_Judge'
END as NFT_name,
nfts_purchased,
CASE maxNFT_Supply
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 10000
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 10
END as NFT_Supply,
CASE maxNFT_Supply
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 100-(nfts_purchased/10000.0*100)
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100-(nfts_purchased/1000.0*100)
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 100-(nfts_purchased/10.0*100)
END as percent_supply_remaining
from poolyContracts
left join
(
Select contract_address as maxNFT_Supply
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1
)
as maxNFT
on maxNFT_Supply = contract_address
ORDER BY 3 desc
Here is where I had to be a little creative. Manually adding numbers to specific table entries in SQL is a difficult undertaking, and I had to apply some tricks to get this to leave a table that is easily readable.
Here again, we will use left join to combine two tables, but we will also used CASE WHEN statements on three of four columns to output specific information that we want to show. What we will do is first create our base table, then create a second table, and left join the second table with the first table and transform the table inputs to make them human-readable as well as do some simple arithmetic for us.
The reason for the second table is that within a single query SQL does not let you call a column twice. We, in fact, need to call one column multiple times and transform each column call separately. A joined table however, allows us to call columns from the second table multiple times, thus enabling us to create the outputs we need for the specific rows in the column that we need.
Let's break this query down into four sections for easier digestion.
TAKE NOTE OF THE SECTION ORDER! 1, 3, 2, 4!
Section 1
In this section we define a table called "poolyContracts" in which we count all individual tokenIds that originated from the null address from the three Pooly contract addresses, thus including only minted NFTs by using the erc721."ERC721_evt_Transfer" table. We then group these by the first column, thus returning minted NFTs per pooly smart contract.
Section 2
In this code block we force the query to only show one of each of the three contract addresses. We do this by using the "group by 1" command, i.e., group the results by unique entries of the first column.
Without the group by command the query would return all transfer events in relation to these contract addresses, but we only need each to appear once. You'll see why in the next section.
Furthermore, we rename the contract_address column to maxNFT_Supply so that we can define which column to join this table onto the poolyContracts table with.
Section 3
This is where the magic happens.
In this section we can now call columns from our joined table. We call:
contract_address
nfts_purchased
maxNFT_Supply
maxNFT_Supply
You'll notice that we are essentially retrieving the same data three times with columns 1, 3 and 4, and that columns 3 and 4 are even the same identical column! This is possible because we joined two tables. If you were to call contract_address twice before the tables were joined, the query editor would return an error message.
Next you will also notice that columns 1, 3 and 4 each have a CASE WHEN clause embedded. Because each of the two previous tables that we created only have one unique line for each smart contract, we cannot use a CASE WHEN statement to specify if a specific smart contract address appears (one of three options), return something else in its place.
You'll see here for the first column we tell the query editor to replace each smart contract address with the name of the respective NFT!
In the third column we replace it with the known max NFT quantities listed on the Pooly website.
And in the fourth column we use a formula to calculate percent of remaining NFT supply. In these statements at least one of the numbers used in the arithmetic operation needs to include one decimal. If this is not included, the SQL query will be interpreted as wanting to return integers, meaning that we will not get any decimals for these calculations. By including “.0” we signify to the server that we want this calculation to return a decimal number.
Section 4
And finally, we indicate that we want the output to be ordered in descending order (largest to smallest) by the third column.
This table is done as well. Save your query, make any changes you need to make to the table and add it to your dashboard.
In our final query, we will create a timeseries chart of the amount of ETH raised through NFT sales over time.
select
block_time as time,
sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth
from ethereum.transactions
where ("to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and date_trunc('day', block_time) < '2022-06-25’
This is a shorter piece of code, however it includes the over command, which is an important command for aggregating cumulative values.
In this query we first select block_time, then we sum the ETH value (which is value/1e18) over the block_time in minute intervals, which we directly sort in ascending order, and name the column cumu_value_eth.
sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth
Furthermore, we've added another filter to the end, which states that for this query blocktime should not exceed 2022-06-25, which is roughly when the fundraising campaign ends according to the Pooly website. This way our area chart will only display data of the campaign, instead of adding a flat line that will drag into eternity as time goes by.
To create the area chart, (1) click on "New visualization", then in the (2) dropdown menu select "Area chart" and finally (3) click "Add visualization".
Your area chart should appear automatically with relevant settings pre-selected by Dune.
If they aren't pre-selected, you can play with the settings below the chart until it looks right.
Finally, save your query and press "Add to dashboard" once more.
Part 3: Cleaning up the Dashboard
We built a lot of queries, and directly added these to our dashboard. Well, let's take a look at how it looks like. Just click on the dashboard name after adding the last chart to your dashboard.
And, let's see…
Yikes! That definitely needs to be cleaned up before it's presentable.
In the top right of your dashboard screen, click "Edit" to start editing.
From here, you can drag and drop the individual elements on a grid that is displayed by a red box in the background while you move elements around, and you can resize each element by dragging the icon in the bottom left corner. Simple!
To add text and images to your dashboard, press "Add text widget" in the top right of the dashboard edit screen.
While cleaning up you might notice that these two queries look the same and having both doesn’t really provide any valuable info…:
Because we don't know when or how PoolTogether is withdrawing the ETH in the smart contract, we can just stick to how the Pooly website does it. We'll remove the right one and replace it with another query.
Here we go, the final dashboard:
That looks much nicer than before, and it also follows the same format of the Pooly website!
Dune Analytics is a strong platform that can provide deep blockchain data capabilities in the right hands. I hope with this piece I was able to teach you the basics. From here, it’s up to you to take on bigger challenges and make even better dashboards. Please share your dashboards with me - I’m excited to see what you build!
If you enjoyed this piece, please consider following me on Twitter @0xPhillan!
Don’t hesitate to reach out if you have any questions about Dune Analytics or ideas for future guides.
Special thanks to @superamscom for reviewing my code and informing me of a bug that has now been fixed!
Learn how to use Dune Analytics, for complete Dune and SQL beginners. Please consider following me on Twitter @0xPhillan
Dune is probably the strongest blockchain data analytics tool currently available to the public, and what's best is: it's free! With Dune you get near real-time access to blockchain data through a public database that you can query through Dune's website using SQL.
That's a lot of power.
Dune decodes blockchain data before adding it to their database, meaning that you don't have to figure out bytecode communication on your own. Instead, you use Dune's dataset explorer to browse datasets, specific smart contracts, events, or calls!
With Dune's recent announcement of their V2 Engine, which increases performance by 10x, it's about time you learn how to use Dune.
In this guide, you will learn:
Part 1: The Dune interface
Part 2: Build your own queries and charts with SQL – starting from the very basics
Part 3: Organizing it all into a dashboard
In this step-by-step guide we will build the queries of below dashboard for the Pooly NFT series:
Let's get started!
Part 1: Overview of Dune and Functionality
Dashboards
Queries
Forking
Query Editor
Dataset Explorer & Data Categories
Saving Your Forked Query
Part 2: Building Your First Query
Deciding Which Queries to Build
Finding the Right Information
Getting Ready to Build Your First Query in Dune
Query 1: Funds Raised in ETH
Query 2: Funds raised in USD
Query 2a: Funds raised in USD at Current ETH Value
Query 2b: Funds raised in USD at ETH Value at Time of Purchase
Query 3: Total Number of Supporters
Query 4a: Leaderboard using the erc721. Abstraction
Query 4b: Leaderboard using the poolysupporter. Decoded Table
Query 5: Max Supply and Remaining Supply of Each of the NFT sets
Query 6: Timeseries Chart of ETH Raised Over Time
Part 3: Cleaning up the Dashboard
Closing
When you first open the Dune website at Dune.com, you are greeted with below window. This window has a view changer at the top that lets you cycle through dashboards, queries and wizards, and then the detailed view area in which you can see the list of dashboards, queries and wizards (users) on the left as well as some search-related settings on the right.
Dashboards are a collection of queries that are arranged as a series of charts, counters, and other information to give the user context about a specific area of interest. Below, I have opened an Ethereum dashboard by the legendary @hildobby. Here we can see all sorts of data that is being pulled from Dune’s database, shown as sums and timeseries charts.
In Dune, every dashboard is public. That means everything you build or other people build can be viewed and forked (i.e., copied) by anybody! This severely decreases dashboard creation time and lets you learn from other users' queries.
If you recall, I mentioned that dashboards are a collection of queries. If you click on the title of any one of the dashboard elements, you'll be taken to that chart's SQL query:
Here in Here we can see two primary on-screen elements: the query (top; black box) and the output chart (bottom). That’s right: no matter which block or chart you click on, you can see how the user created that chart!
If you want to save an entire dashboard or only the chart’s query to your own account, you can click “Fork” on the top right, and everything on the forked screen will be duplicated to a new window, in which you can make edits before saving the views to your account.
Let's fork the Ethereum Price chart! Once you press "Fork" on a query, you'll be taken to the query editor with the previous code already copied in!
Let me introduce you to the various on-screen elements here:
Query location & name – name can be changed once you hit save!
Dataset explorer – search for a specific dataset
Query window – input your SQL queries here
Visualization selector – choose whether to see query results, the forked line chart or create a new visualization
Run – run the query that is in the query window
Results/Visualization – see query results or your visualization created with the query results
Save – save your (forked) query!
Let's take a closer look at the dataset explorer. There are six functional areas within the dataset explorer:
Chain selection
Dataset search
Browse raw blockchain data
Browse data of decoded contracts
Browse abstractions
Browse community-supplied data
In the dataset selection you can choose which chain you want to parse. Selecting "Dune Engine V2 (Beta)" let's you use Dune's latest enhancements which include multichain queries and 10x performance increases.
If you choose another chain, you the category selection (items 3-6 in previous image) will disappear and instead you'll see a list of contract calls and events that you can interact with.
In the search field you can input your search parameter, and Dune will search through all tables that include that keyword in some form.
Note: The Dune Engine V2 and the old search function return results in a different fashion. The old search returns a list of all results, while Dune Engine V2 returns a nested list of results. We will be using the V2 engine!
If you click into raw blockchain data, you can easily find queries for the various blockchains that Dune supports in a nested data structure where you can first pick your raw table, and from there pick the specific table columns you want to investigate further. Within each level of nesting you are also given the option to filter for specific search results you are looking for.
This is a very quick and handy way to get high level blockchain data.
In here you will find projects that have been decoded by Dune. Decoded projects are projects that the Dune team took apart, labelled, and put into tables so that users have an easy and standardized reference for certain data.
You will notice that again, the search results are nested. At the highest level, we have projects that you can search through, at a lower level you can filter specific smart contracts within that project, and finally we get to the various tables generated from that smart contract. If you click on any of the tables, you will see a list of columns, just like in with the raw blockchain data.
Abstractions can be thought of as custom tables that connect and combine various queries and pieces of data to form a unique table. Abstractions help users to query specific data they are looking for more easily, without the trouble of manually combining various pieces of data.
Generally, abstractions can be split into two main categories:
Sector abstractions: sector-specific data
Project abstractions project-specific data
From the abstractions sub-menu, we can see a list of abstractions with labels that specify whether the abstraction is sector-specific or project specific.
The community section can be thought of as an extension of the abstraction section, but with data aggregations being supplied by Dune community members.
You may wonder why there is only one entry in the community section ("flashbots") – well, that's because Dune Engine V2 was just released! Over time, we can expect to see more and more community-built datasets by trusted community members.
In below illustration you can see a summary of how data is summarized within Dune as of launch of Dune Engine V2: the four main data categories are raw blockchain data, decoded projects, abstractions, and community, which hold the data of various blockchains in table format that can hold various data types.
Let's go ahead and save this query first. After you hit save, a few things will happen. First, you will be asked to give your query a name.
Once you've chose a name, you will notice (1) the query location and name have been updated to your chosen name and (2) your query is running. That means Dune is fetching the latest data from their database, which is periodically updated with the latest data from various blockchains.
Once the query is finished running, you'll see your query results (3).
From here, if you click any of (1) "Query results", "Line Chart" or "New visualization", the (2) result/visualization box will update together with (3) settings for your selection that appear beneath it. Here you also have an "Add to dashboard" button, to quickly add your query results or visualization to new or existing dashboards – just like @hildobby's Ethereum dashboard from earlier!
If you click on (1) the circle in the top right and then on (2) “My queries”, you will open the queries list of your account.
The queries list includes all queries you’ve ever saved on your account. In below screenshot at the top we can see the latest query created:
Congratulations, you've forked and saved your first query with a visualization!
Forking is a Dune superpower, and it can help you easily and quickly create new queries by building on what other wizards (yes, you're a wizard now too!) have built before you. You can combine multiple forked queries to build your own dashboard!
Let's get our hands dirty and build a dashboard – a collection of queries and visualization – from scratch without forking. This will teach you where to find the right blockchain details to look for your specific project as well as teach you SQL basics.
The purpose of this section is to teach you:
How to find the right information you need for your specific project
Some basic SQL
But first, we need to decide what we want our dashboard to be about. Pooly NFTs by the Pool Together DeFi protocol are a great first step.
If we search for "Pooly" on Dune, sure enough we can find some Pooly NFT trackers created by the community.
We could click into one the Pooly dashboard created by @0xbills and click on "Fork" and work from there…
However, if we build it from scratch we get to learn how to be a blockchain detective and learn some SQL as well! So instead, we'll build our own from scratch.
First, let's decide what charts we want on our dashboard. Let's rebuild the views that Pooly built on their homepage! Taking a closer look at below two screenshots, we can see a few indicators based on on-chain data.
We can see:
Funds raised vs funding target denominated in ETH
Funds raised vs funding target denominated in USD
Total number of supporters (unique addresses that purchased a Pooly)
Leaderboard including addresses, number of NFTs purchased per address and total ETH spent in descending order
Max supply and remaining supply of each of the three NFT types
Awesome! But those are only a snapshot in time. Let's give ourselves another challenge as well:
Timeseries chart of ETH raised over time
As it stands now, we can't build the views in the same way as the Pooly website, but we can capture the same amount of data (and more!) to build our dashboard.
Before we get started on Dune, we need to find the right information. From the website we can tell that PoolTogether is selling three sets of NFTs:
Supporter – 1 of 9 random collectible for 0.1 ETH
Lawyer – Only one artwork for 1 ETH
Judge – Only one artwork for 75 ETH
Is Pooly selling all three NFTs through one contract, or through three different contracts?
Let's head to Etherscan and see if we can find a smart contract related to Pooly. Once you open Etherscan.io type "Pooly" to see if the owner of these smart contract registered them with Etherscan.
Indeed! There are three smart contracts, likely corresponding to each of the three NFT collections. Furthermore, we now know that each Pooly is an ERC721 token.
Open each of the three collections and copy the smart contract address by clicking on the copy icon that appears upon hovering over the address. At the bottom of the page we can see all recent transactions as well, which will be helpful for troubleshooting later.
We will need these smart contract addresses to pull the right data from Dune, and they form the foundation of all of our queries:
0.1 ETH Pooly Supporter:
1.0 ETH Pooly Lawyer:
75 ETH Pooly Judge:
First, navigate to dune.com and click "New Query" on the top right of the screen.
This will open the query editor and we can start working on our query!
First, in the top left switch from "7. Dune Engine V2 (Beta)" to "1. Ethereum". Pooly lives on Ethereum, thus we only need Ethereum data for this query. Plus, the tables in "1. Ethereum" are more mature than than Dune Engine V2, which only just entered beta.
For our first query, we will build a counter that shows funds raised denominated in ETH. To do this, copy below code into the query field in Dune and press "run" (or CTRL+Enter):
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
The above code is an SQL query that parses through Dune's database to fetch specific data that we requested. You can imagine Dune's database to be a collection of various tables, each containing specific information you may want to pull. With SQL, you…
specify what data you want (which column in a table)
whether you want to transform that data
from which table you want to get the data
whether you want to filter the data
To illustrate the above, let us run the above code piece by piece. Copy the below code into Dune's query editor and run it:
select * from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
You will get a big table with a lot of information:
Now let's walk through the SQL code:
What this code says is "select all columns from the transactions table within the ethereum category where the value in the to column is \x3545192b340F50d77403DC0A64cf2b32F03d00A9" or in plain English: show me a table with all smart contract interactions with the Pooly2 (1 ETH) smart contract.
You don't need to run a query to see the columns within a table. The data explorer let's you explore the various table headers through their nifty search function:
We could have removed line 3 entirely to remove the filter, however, this would have returned a massive table and the query would have taken a long time to complete. The more precise your queries, the faster they will run!
Because we only want to return funds raised, we don't need all columns. So let's adjust our code to only grab the "value" column:
select "value" from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
Now we only have one “value” column instead of the many we saw before:
You may notice, however, that the values seem to be quite large. That's because they are denominated in Wei and not ETH! To fix that, we can simply apply an arithmetic operator to the "value" column:
select "value"/1e18 from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
That looks better! 1e18 in SQL is the same as 10^18, and we are simply telling Dune to divide the number by 1,000,000,000,000,000,000 so that instead of Wei, we see the values denominated in ETH.
And since we only want the total value and not a list of value, we can wrap the "value"/1e18 in a SUM() statement:
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
Awesome, we can now see the total number of ETH spent on Pooly2! And since we want to get the total spent on all three Pooly NFT smart contracts, we need to add two more lines to include details about the other smart contracts:
select SUM("value"/1e18) from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
The "or" command works in tandem with the "where" command and specifies that when filtering the values in the "to" column, that the row should be considered if the first value or the second value or the third value are found.
We now see that in total 773.7 ETH has been spent on all three Pooly contracts. Awesome! Let's go to the Pooly website to see if that's right:
On the Pooly Website we see 776.5 ETH - congrats on achieving the target! But, oh no, there's a 2.8 ETH difference! Well – that's nothing to worry about. Dune syncs blockchain data in regular intervals. And because the dataset they sync to their database is so massive, it takes some time. We can expect the data to be refreshed in the next hour or two.
Now that our query is complete, we need to set up a counter to display this on our dashboard later. Below the Query results box, click on new visualization, and in the drop down menu that appears click on "counter".
And finally, click on "Add visualization":
A counter will appear and if you scroll down, you'll see various settings show up. Simply adjust the settings to your liking.
Once done, click on (1) "Add to dashboard" and select (2) "New dashboard". Then (3) give your dashboard a name and (4) click "Save dashboard". Your new dashboard will appear in your dashboards list. From here click (5) "add" on the dashboard you wish to add your visualization to. Once added, the label will change from "add" to "added".
If you click on your dashboard's name in this sub-menu ("Pooly NFT by 0xPhillan"), you will be taken to a dashboard that has our tracker displayed.
Great!
We'll get back to editing our dashboard once we have completed setting up all queries.
There are two ways that we can approach this:
Use the current value of the funds in USD used to purchase the NFTs
Use the USD value of the funds at the time of purchase
If we look at the smart contracts on Etherscan, we can see that a large portion of the 776.5 ETH have already been moved out of the smart contracts, leaving 299.2 ETH in the Pooly NFT smart contracts as of time of writing.
If we look at the Pooly website screenshot from earlier 776.5 ETH is valued at $1,411,249 USD ($1,817 USD/ETH), hinting that possibly the Pooly smart contract owner is keeping the funds as ETH instead of trading for USD.
Ultimately, it's difficult to say which approach Pooly is taking, but both approaches to USD value are interesting:
The current value tells us what the funds are worth now
The value at time of purchase tells us the intended USD donation amount by purchasers
So… let's create both!
For this one, we'll use our previous code as a base and slot in some additional lines to get the current USD value.
First, fork the query we just created:
Then adjust your code to look as follows:
select SUM("value"/1e18) * (
SELECT "price" FROM prices.usd
WHERE "symbol" = 'WETH'
AND "minute" < now() - interval '1 hours'
ORDER BY "minute" DESC
LIMIT 1
)
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
You'll notice that we have added a multiplication operator * and a large code block right after our SUM("value"/1e18) command.
In Dune, you can highlight specific portions of your query and only run that portion by clicking "Run selection". Let's (1) highlight only the lines within the parentheses and (2) run that selection:
In the query results you'll see the latest USD price of WETH! Our addition here multiplies the latest price of WETH with the amount of ETH raised to give us the USD value.
Let's break down this code block:
From the prices.usd table select the "price" column
Filter the symbol column for "WETH"
Only look at time entries of the past 1 hour (this will significantly speed up the query)
Order in descending order (newest first)
Limit the query to one result (the first result, i.e., latest price)
To better understand this block of code, let's make some small adjustments to our query. (1) replace "price" with * (which returns all columns) and (2) select only the code from lines 2 to 5, then (3) run the selection:
In the query results you will see the full table consisting of five columns. First, let's check the contract address in Etherscan.io:
This smart contract controls the WETH asset on the Ethereum blockchain. Great! Now we know the source of the table from our previous screenshot that shows the WETH price in USD.
Let's divert our attention back to the previous table:
In here we have a column called "minute" which tracks the ETH to USD value every minute. Since we limited the query to "interval – '1 hours'", we only get the latest hour of data available. Since for our purposes we actually only need the latest data entry, limiting this query to the last hour speeds up the query significantly. You could also change this to '1 days', '3days' or '1 weeks' for example, to get more historical data.
What's important here is that the columns name is "minute", hence our query refers to the "minute" column, which is not to be mistaken for a time-related command.
Let's revert our code back to what we changed it to at the beginning of this section and run the query:
The result is the current USD of the ETH that has been transferred to the Pooly1, Pooly2 and Pooly3 smart contracts in exchange for Pooly NFTs.
For this we will be using a counter again, so scroll down and (1) click on the counter forked from our previous query, (2) adjust the data source and (3) change the labels.
Once finished, remember to save and add to our dashboard:
After it's added, it will look like the below screenshot. Don't worry, at the end of this guide we'll clean it up. For now, don't worry about the looks!
This query will be a little more complex, as we will have to query two tables and combine the results. Specifically, we will have to get the individual transactions and convert the ETH value of each transaction using the price of ETH at the time of the transaction.
Again, let's first fork our previous query in preparation for our next query:
From the forked code, we want to make the following:
with poolyTransactions as
(
select
block_time,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
sum(value_eth * price)
from poolyTransactions tx
left join
(select minute, price from prices.usd
where symbol = 'WETH' and minute > '2022-05-01')
as prices on date_trunc('minute', block_time) = minute
If we run this code here, we see that we receive a USD value of 1,4m USD. This is the result of
Let's break this code down into three sections:
Section 1
Here we build the first table that we will reference. What we do here is create an auxiliary table that we call "poolyTransactions" that will hold block_time and value_eth (value in wei divided by 10^18 to convert to ETH, which we give a custom name) from the ethereum.transactions table. For this table, we filter the three Pooly addresses that we know of.
Here a line by line explanation:
Line 1: with poolyTransaction as – define auxiliary table called "poolyTransaction" as having below properties
Line 3-11: select columns and filters to include on the ethereum.transcations table
Line 5: value/1e18 as value_eth – here we rename the column as "value_eth" so that we can reference it directly in section 2 instead of doing another calculation
Section 2
This is where we create the output table. You'll notice that we are building a table from poolyTransactions, the auxiliary table we created in section 1, but we are also referencing a column called "price" that we didn't define yet. Price is in fact only defined later in line 19! This is made possible, because we are joining the poolyTransactions with certain outputs from the prices.usd table in section 3. So in essence, we are creating a table using our auxiliary table poolyTransactions as well as the table we built from prices.usd in the next section.
Section 3
This is where we define a table that we want to join with another table. The "left join" keyword allows us to do this:
Line 18: left join – keyword used to indicate that we want to join our first table (left table) with another table (right table). That means, our first table defined in section 1 acts as the base table.
Line 19-20: here we define the table we want to create from prices.usd. In line 20 we limit the duration to '2022-05-01', because the Pooly smart contracts were only deployed in the month of May, so we can significantly speed up the table query if we limit it to a smaller timeframe instead of parsing data all the way to Ethereum's genesis block.
Line 21: as prices – this names our table in lines 19-20 "prices" for easier future reference
Line 22: on data_trunc('minute', block_time) = minute – this is the line that combines our auxiliary table (section 1) with the prices table (section 3 lines 19-20). What it says here is to take column "block_time" from our auxiliary table, and truncate it to minutes only, i.e., remove all other data that is not minutes (e.g. seconds, milliseconds, etc.). The prices.usd table is already truncated to minutes, so there is no further conversion needed here. Then the minute column in prices.usd is matched to the minute column in our auxiliary table, and the correct price is thus allocated from the prices.usd to the corresponding minute timestamp in poolyTransactions.
To better visualize the third section, I have reorganized the parts to make it easier to understand:
(1) We create the poolyTransactions table, then (2) we tell SQL to join it with another table, which (3) we define as minute and price columns from the prices.usd table. This prices.usd table we created is then (4) joined onto the left table poolyTransactions using the time in minutes as a mapping variable. To join tables, both tables must have entries which are exactly the same and if we truncate the block_time variable to minutes, we create matching minutes between both tables. By doing this, the (5) poolyTransactions table is updated to include the price column, with price values being matched to the respective dates.
From here, we simply query the joined poolyTransactions table and sum the result of multiplying the value_eth and price of ETH for each row.
Now add a counter, save and add to dashboard!
For our next query, we want to count the unique addresses that purchased a Pooly NFT (i.e., total number of supports). That means that even if an address purchased multiple Poolys across all three Pooly types, they should only be counted once.
For this, let's first open our very first query, fork it, and remember to save it too.
Here we simply change the first line:
select COUNT(DISTINCT "from") from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
The COUNT variable counts all transactions, and the DISTINCT keyword makes sure that every unique entry is only counted once. The result we get is 4660 unique supporters. If we compare that to the unique supporters on the Pooly website, we see they are very close:
This shows that our query is correct, as there is a little lag between Dune's database refreshes and the latest blockchain state.
Finally, change the counter visualization and add to dashboard again.
Next, let's build the leaderboard including addresses, number of NFTs purchased per address and total ETH spent in descending order.
Again, let's fork our previous query so that we avoid having to retype the filtered addresses. Remember to save this new query before proceeding.
Looking at the leaderboard, there are three pieces of information we need. First, the address of the purchaser, then the number of NFTs purchased, and finally, the amount of ETH spent on all NFTs purchased.
Here we are looking at the NFTs purchased not at the NFTs held. It is entirely possible that somebody purchased (minted) an NFT then moved it to a safe wallet or resold it at a later point. We are only interested in first-time purchases (mints).
We achieve this using below query:
with poolyTransactions as
(
select
"from",
hash,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
"from", nfts_purchased, value_eth
from poolyTransactions
left join
(Select evt_tx_hash, COUNT("tokenId") as nfts_purchased
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1)
as nfts
on evt_tx_hash = hash
ORDER BY 3 desc
You will notice that this heavily resembles the query in "Funds raised in USD at ETH value at time of purchase", and that's because we are using the same methodology: we first gather the transaction data in our poolyTransactions table, then we left a second table on it with a common mapping value.
Here, for the second table we use the erc721."ERC721_evt_Transfer" table, which is an abstraction that Dune maintains that tracks all NFT transfers on Ethereum. If we use the dataset explorer, type "erc721." And scroll to "ERC721_evt_Transfer" we can see everything that is included in that specific table. We can also highlight only the commands for the second table and see what the output would be:
Since we only want NFTs that were newly minted by the smart contract, we must specify the "from" address to be the null address. On Ethereum, all NFTs are minted from the null address. By counting the amount of "tokenId" for each transaction, we can count the total number of NFTs minted for each transaction.
You will also notice something peculiar about how the filters are defined. The first three filters are now enclosed in brackets, while the last filter is outside of the brackets.
The brackets determine the order in which the and/or filter commands are evaluated, just like when executing arithmetic commands in SQL. If we did not enclose the first three statements, the and condition would only apply to the last filter setting.
Since we want the from null address filter to be applied to all results of the previous filters, we need to add the parentheses.
Finally, since we are using the "COUNT" command, we need to specify what column to count these on (i.e., what variable to roll-up the counts to). For this we use the "group by" command to say that we want to group the count of "tokenId" to the first column in our table, which is "evt_tx_hash".
Earlier I mentioned that we need a common mapping value to map this second table to the transactions table. Here we use the transaction hash to map the number of NFTs purchased per transactions, to our poolyTransactions table where this time we also requested the transaction hash. So ultimately, we are mapping the transaction hash of our erc721."ERC721_evt_Transfer" table (which we named "nfts") to the transaction hashes of our poolyTransactions table, which only include transactions that were used to purchase poolys.
The output is a table that includes the purchasers address, the number of NFTs that purchased purchased in total, as well as the total value of ETH spent.
Finally, we tell Dune to "ORDER BY 3 desc", which means that the third column of our output table should be ordered in descending order:
Awesome! Our leaderboard is complete. Let's compare it against the leaderboard on the Pooly NFT website:
Not all figures tie, but from this list we can see that a few addresses, NFTs purchased and total ETH spent figures do tie. This is once again a sync timing issue between Dune and real-time blockchain data and nothing to worry about.
Remember to save your query and add it to the dashboard.
Instead of using the erc721."ERC721_evt_Transfer" table, we can also use the poolysupporter."PoolyNFT_call_mintNFT" decoded table that the Dune team put together.
with poolyTransactions as
(
select
"from",
hash,
value/1e18 as value_eth
from ethereum.transactions
where "to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
select
"from", nfts_purchased, value_eth
from poolyTransactions
left join
(Select call_tx_hash, "_numberOfTokens" as nfts_purchased
From poolysupporters."PoolyNFT_call_mintNFT"
where contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523'
)
as nfts
on call_tx_hash = hash
ORDER BY 3 desc
The methodology is the same as above, except that with this table we can directly return all transaction hashes that called the mintNFT function, instead of using the null address to determine which transactions from the erc721. "ERC721_evt_Transfer" table are mint transactions.
The poolysupporter dataset allows us to do more specific and detailed queries, as we can refer to specific contract calls.
Let's compare the results of our two tables to ensure nothing is amiss:
Won't you look at that, the outputs are identical. Great!
Remember to save your query and add it to the dashboard.
In the alternative version of query 4 we used the poolysupporter function. You may have seen that when you search for pooly in the dataset explorer, you also see a function called "PoolyNFT_call_maxNFT".
You may conclude that you can use this function call to directly retrieve the maximum minted NFTs.
Unfortunately, that is not possible: this function is a "read" function and as a result, there is no on-chain record when this function is called. See Etherscan below:
The maxNFT variable is set when the deployer contract deploys the Pooly Supporter smart contracts, but unfortunately at time of writing, the deployer smart contract has not been decoded, so we can't get the max mint numbers from on-chain data – at least not without significant effort.
Instead, we'll have to manually slot in the maxNFT figures for each smart contract:
with poolyContracts as
(
Select contract_address,
COUNT("tokenId") as nfts_purchased
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1
)
select
CASE contract_address
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 'Pooly_Supporter'
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 'Pooly_Lawyer'
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 'Pooly_Judge'
END as NFT_name,
nfts_purchased,
CASE maxNFT_Supply
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 10000
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 10
END as NFT_Supply,
CASE maxNFT_Supply
WHEN '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed' then 100-(nfts_purchased/10000.0*100)
WHEN '\x3545192b340F50d77403DC0A64cf2b32F03d00A9' then 100-(nfts_purchased/1000.0*100)
WHEN '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523' then 100-(nfts_purchased/10.0*100)
END as percent_supply_remaining
from poolyContracts
left join
(
Select contract_address as maxNFT_Supply
From erc721."ERC721_evt_Transfer"
Where (contract_address = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or contract_address = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or contract_address = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and "from" = '\x0000000000000000000000000000000000000000'
group by 1
)
as maxNFT
on maxNFT_Supply = contract_address
ORDER BY 3 desc
Here is where I had to be a little creative. Manually adding numbers to specific table entries in SQL is a difficult undertaking, and I had to apply some tricks to get this to leave a table that is easily readable.
Here again, we will use left join to combine two tables, but we will also used CASE WHEN statements on three of four columns to output specific information that we want to show. What we will do is first create our base table, then create a second table, and left join the second table with the first table and transform the table inputs to make them human-readable as well as do some simple arithmetic for us.
The reason for the second table is that within a single query SQL does not let you call a column twice. We, in fact, need to call one column multiple times and transform each column call separately. A joined table however, allows us to call columns from the second table multiple times, thus enabling us to create the outputs we need for the specific rows in the column that we need.
Let's break this query down into four sections for easier digestion.
TAKE NOTE OF THE SECTION ORDER! 1, 3, 2, 4!
Section 1
In this section we define a table called "poolyContracts" in which we count all individual tokenIds that originated from the null address from the three Pooly contract addresses, thus including only minted NFTs by using the erc721."ERC721_evt_Transfer" table. We then group these by the first column, thus returning minted NFTs per pooly smart contract.
Section 2
In this code block we force the query to only show one of each of the three contract addresses. We do this by using the "group by 1" command, i.e., group the results by unique entries of the first column.
Without the group by command the query would return all transfer events in relation to these contract addresses, but we only need each to appear once. You'll see why in the next section.
Furthermore, we rename the contract_address column to maxNFT_Supply so that we can define which column to join this table onto the poolyContracts table with.
Section 3
This is where the magic happens.
In this section we can now call columns from our joined table. We call:
contract_address
nfts_purchased
maxNFT_Supply
maxNFT_Supply
You'll notice that we are essentially retrieving the same data three times with columns 1, 3 and 4, and that columns 3 and 4 are even the same identical column! This is possible because we joined two tables. If you were to call contract_address twice before the tables were joined, the query editor would return an error message.
Next you will also notice that columns 1, 3 and 4 each have a CASE WHEN clause embedded. Because each of the two previous tables that we created only have one unique line for each smart contract, we cannot use a CASE WHEN statement to specify if a specific smart contract address appears (one of three options), return something else in its place.
You'll see here for the first column we tell the query editor to replace each smart contract address with the name of the respective NFT!
In the third column we replace it with the known max NFT quantities listed on the Pooly website.
And in the fourth column we use a formula to calculate percent of remaining NFT supply. In these statements at least one of the numbers used in the arithmetic operation needs to include one decimal. If this is not included, the SQL query will be interpreted as wanting to return integers, meaning that we will not get any decimals for these calculations. By including “.0” we signify to the server that we want this calculation to return a decimal number.
Section 4
And finally, we indicate that we want the output to be ordered in descending order (largest to smallest) by the third column.
This table is done as well. Save your query, make any changes you need to make to the table and add it to your dashboard.
In our final query, we will create a timeseries chart of the amount of ETH raised through NFT sales over time.
select
block_time as time,
sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth
from ethereum.transactions
where ("to" = '\x90B3832e2F2aDe2FE382a911805B6933C056D6ed'
or "to" = '\x3545192b340F50d77403DC0A64cf2b32F03d00A9'
or "to" = '\x5663e3E096f1743e77B8F71b5DE0CF9Dfd058523')
and date_trunc('day', block_time) < '2022-06-25’
This is a shorter piece of code, however it includes the over command, which is an important command for aggregating cumulative values.
In this query we first select block_time, then we sum the ETH value (which is value/1e18) over the block_time in minute intervals, which we directly sort in ascending order, and name the column cumu_value_eth.
sum(value/1e18) over (order by date_trunc('minute', block_time) asc) as cumu_value_eth
Furthermore, we've added another filter to the end, which states that for this query blocktime should not exceed 2022-06-25, which is roughly when the fundraising campaign ends according to the Pooly website. This way our area chart will only display data of the campaign, instead of adding a flat line that will drag into eternity as time goes by.
To create the area chart, (1) click on "New visualization", then in the (2) dropdown menu select "Area chart" and finally (3) click "Add visualization".
Your area chart should appear automatically with relevant settings pre-selected by Dune.
If they aren't pre-selected, you can play with the settings below the chart until it looks right.
Finally, save your query and press "Add to dashboard" once more.
Part 3: Cleaning up the Dashboard
We built a lot of queries, and directly added these to our dashboard. Well, let's take a look at how it looks like. Just click on the dashboard name after adding the last chart to your dashboard.
And, let's see…
Yikes! That definitely needs to be cleaned up before it's presentable.
In the top right of your dashboard screen, click "Edit" to start editing.
From here, you can drag and drop the individual elements on a grid that is displayed by a red box in the background while you move elements around, and you can resize each element by dragging the icon in the bottom left corner. Simple!
To add text and images to your dashboard, press "Add text widget" in the top right of the dashboard edit screen.
While cleaning up you might notice that these two queries look the same and having both doesn’t really provide any valuable info…:
Because we don't know when or how PoolTogether is withdrawing the ETH in the smart contract, we can just stick to how the Pooly website does it. We'll remove the right one and replace it with another query.
Here we go, the final dashboard:
That looks much nicer than before, and it also follows the same format of the Pooly website!
Dune Analytics is a strong platform that can provide deep blockchain data capabilities in the right hands. I hope with this piece I was able to teach you the basics. From here, it’s up to you to take on bigger challenges and make even better dashboards. Please share your dashboards with me - I’m excited to see what you build!
If you enjoyed this piece, please consider following me on Twitter @0xPhillan!
Don’t hesitate to reach out if you have any questions about Dune Analytics or ideas for future guides.
Special thanks to @superamscom for reviewing my code and informing me of a bug that has now been fixed!
No activity yet