Explore Ethereum Data with SQL Queries on Dune Analytics

This tutorial will teach you how to query onchain data using the amazing Dune Analytics platform. Dune Analytics is the all-in-one destination to query, visualise, share and explore human readable smart contract data. It’s trusted by a lot of awesome companies like Gnosis, Compound and Bancor.

For using the platform to make your own queries you’ll need to create an account. The good news is that it’s free! If you want direct API access or make private queries you’ll need to sign up for the premium solution.

Once you signed up for an account, you’ll land on the page, the best way to get you started is to explore existing queries by clicking Queries on the top left. You’ll then see the latest public queries that were created by others:

From this page, you can explore queries by latest or chose a tag that is interesting for you. We suggest you to get started with a simple query to understand the query language. For example this query that list Token transfers of a specific token. On the page of a query you’ll see the result of the query and you can click on Edit query to see the code.

The code of this query looks like this:

SELECT 
       topic2 AS sender,
       topic3 AS receiver,
       bytea2numeric(data)/1e18 AS value
FROM ethereum.logs
WHERE
 topic1='\xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer
AND
 contract_address = '\x0d8775f648430679a709e98d2b0cb6250d2887ef' -- BAT
LIMIT 10

Dune Analytics offers you access to raw Ethereum data like block, logs (events), transactions, receipts, traces (contract messages). And also decoded data like ERC20 transfers and other specific protocol data. This query uses the raw log data to find out the ERC20 transfers of the BAT token. We’ll first see how it works and later see how the same query can be achieved using the decoded ERC20 transfer type. You can read more about how to write queries in their documentation.

To understand how this query works we have to understand the anatomy of a Transfer event emitted by ERC20 contracts. The event looks like this:

event Transfer(address indexed from, address indexed to, uint256 value);

Which means that when the event will be emitted during a token transfer we’ll have in the log table a row containing:

• contract_address: the address of the ERC20 token
• topic1: The hash of the encoded contract (Read here how it works) For ERC20 the hash is 0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef
• topic2: the first indexed parameter from
• topic3: the second indexed parameter to
• data: The row data of the event, in our case the last parameter which is the amount of token sent (value).

Note that in lot of platforms the hash of the log is considered as topic0 but on Dune Analytics it is topic1.

You can see an example of a transfer event on Etherscan:

If we come back to the query code we’ll see line 4 that the bytea2numeric function is used to convert te hexadecimal value of the amount to a decimal numeric value.

Line 9 the contract_address is filtered to only show events that are emitted by the BAT ERC20 token at address: 0x0d8775f648430679a709e98d2b0cb6250d2887ef.

On the left side of the query editor, you’ll see a list of already available data. For convenience a lot of protocols have already been decoded so you can directly use the available properties for your queries:

As said before, Dune Analytics also offers you direct access to ERC20 transfers so we’ll try to rewrite the previous query using the decoded transfers. To do this, create a new query or fork the existing one.

In the left panel we’ll search how ERC20 transfers are structured:

So to list all the Transfers of the BAT token we can write something like this:

SELECT "from", "to", "value"
FROM "erc20"."ERC20_evt_Transfer"
WHERE contract_address='\x0d8775f648430679a709e98d2b0cb6250d2887ef' --BAt Token Address
-- ORDER BY "evt_block_number" DESC
LIMIT 10

We commented the ORDER BY clause as the query is longer with it and as we’re testing the tool we prefer to be able to test as fast as possile or queries.. The query gives a result like this:

As you can see, the amount of the transfer does not take into account the number of decimals of the token. If you are not sure about how ERC20 tokens works and what is the number of decimals you should read our article about it. For this, luckily there is another table that contains all of the ERC20 tokens and their number of decimals: “erc20.tokens”. So we’ll do a simple join to get the real value like this:

SELECT "from", "to", "value" /10^("decimals") as "value"
FROM "erc20"."ERC20_evt_Transfer", "erc20"."tokens"
WHERE "ERC20_evt_Transfer".contract_address='\x0d8775f648430679a709e98d2b0cb6250d2887ef' --BAt Token Address
AND "tokens".contract_address="ERC20_evt_Transfer".contract_address
-- ORDER BY "evt_block_number" DESC
LIMIT 10

With a result looking like this:

---

As a conclusion, Dune Analytics is a really powerful tool and free that make it easy for anyone to explore and play with Ethereum on chain data. Don’t hesitate to comment with your favorite queries and what you use Dune Analytics for.

Try Dune Analytics

See the documentation

We hope you enjoyed this tutorial, stay tuned as we’ll soon write how we used Dune Analytics to understand how people are using flashloans.