So you want to write a Dune query for some quick and dirty analysis, but the contract isn’t verified on Dune! *starts sobbing uncontrollably 😭😭*
But degens aren’t deterred! With a little understanding of Ethereum events and a little effort, you can be on your way to querying.
I realized partway through writing this tutorial that Richard Chen already used the methodology I’m discussing to create a query for summing the amount given by each contributor to ConstitutionDAO, so to give due credit to the Gandalf of Dune wizards, I’ll be breaking down the steps to get to his result.
Events (also known as logs) on Ethereum are a way of storing historical information that is significantly cheaper than contract storage. While this data can’t be accessed by contracts, they can be easily queried from an application.
They are typically used as records of pertinent information from a transaction, so they are also great for analysis.
In this example, we’ll look at
Pay events from the Terminal V1 contract, which have information on who paid into the DAO and how much. I pasted an example of one of these events below from Etherscan.
Dune Analytics has a table of all the events that take place on Ethereum--
ethereum.logs. But how do we get the events we want?
Events have up to 4 indexed parameters to make querying from a node easier and faster. Dune stores these in
The first topic is almost always the “event signature,” an identifier for the event.
There are two ways to get this:
For the first method, if you have an example transaction and the contract is verified on Etherscan, you can grab the first topic from the event log. In our on-chain example, you can see that the signature is `0x3deb3bb81c337489381f7685daa204d30e6a265dac443a718949c258e3e17317`.
Say you don’t have an example transaction handy or the contract isn’t verified on Etherscan, doesn’t matter if you’re a degen! If you know the event name and parameter types, you can compute the signature yourself!
The signature is the keccak256 hash of the event name and argument types:
keccak256('eventName(arg1 type,arg2 type…)')
Pay event, it’s:
Using node and `ethers`, we can run:
const ethers = require('ethers'); ethers.utils.keccak256(ethers.utils.toUtf8Bytes('Pay(uint256,uint256,address,uint256,string,address)'));
We get the same result:
Finally, we can use the event signature and contract address to fetch these events in Dune:
Now that we have the event data, we can start to look at some numbers.
Looking back at our sample event, let’s look at what information we have:
We want to see the contributions to ConstitutionDAO by contributor. You can see there is a
projectId field on which we’ll want to filter, a
beneficiary field showing who contributed (and therefore should be the beneficiary of DAO tokens), and an
amount that we’ll want to sum.
ethereum.logs has 4 columns (topic1-topic4) for each of the (up to) 4 indexed fields.
projectId will be in
beneficiary will be in
amount is concatenated with the other non-indexed parameters in the
You’ll notice that the data fields (
data) are not nice decoded values. Rather, everything is a bytes data type.
For addresses, we want to the hex string anyway, so all we want to do is effectively cut out the leading zeros in the bytes with the
substring function like so:
SUBSTRING(topic4 FROM 13 FOR 20) AS address_from_topic4
projectId, we want to get a numeric value. We can use the
bytea2numeric function to convert the raw bytes to a decimal number like so:
BYTEA2NUMERIC(topic3) AS project_id_from_topic3
Last but not least, we need to combine the previous two functions to get the
amount parameter. Since
amount is one of the non-indexed parameters, it is concatenated with the other non-indexed parameters (
amount is the first parameter and it’s 32 bytes long, we can clean it up like so:
BYTEA2NUMERIC(SUBSTRING(data FROM 1 FOR 32)) AS amount_wei_from_data
You’ll notice that this is a large number, because it is the amount of ether specified in its smallest denomination--wei. We need to divide it by 10^18 to get the amount in ether:
BYTEA2NUMERIC(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_ether_from_data
We can check all these parameters are correct by comparing our query with our sample event.
If we apply everything discussed herein, we end up with Richard Chen’s query for the largest contributors:
Go forth and query, my fellow degen wizards.
One thing to watch out for when doing this is are parameters that aren’t a fixed width in bytes. For example, in the
Pay event, there is a
note field of type
string type can be an arbitrary length, so if you’re querying in the data field for a parameter after it (like
caller), it takes some extra tricks. I’ll leave that to you to figure out :)