Alex KroegerA modest proposal: USDC v3
TLDRCentre’s USDC has become the second largest stablecoin in DeFi. While it has maintained its 1:1 peg with the US dollar well, its “blacklist” functionality comes with a heavy tax on users, amounting to, by my estimate, an extra $3.6M spent in transaction fees in December 2021 alone. I propose a new version of USDC that preserves what I argue is the most salient feature of the blacklist--the ability to freeze funds--in a way that eliminates blacklist-related gas costs for users, which can b...
Alex KroegerThe US government as a smart contract
A technical guide to on-chain governanceDisclaimer: this post and accompanying code is meant for educational purposes only. None of this is audited, and you should seek out audits before using contracts in production. There has been much ado about DAOs lately--they are being used to govern financial protocols, form modern day guilds, and crowdfund the sale of historical documents. But how do they work on-chain? This post will walk through an example of setting up governance using a variant of...
Alex KroegerDAOs and the Free-rider Problem
The free-rider problem arises when a good or service has private costs and non-excludable public benefits. A classic example is a firework show. Fireworks can be enjoyed by anyone in a particular area regardless of whether they pay for it (it’s hard to stop people from looking at the sky if they don’t have a ticket). Lots of folks may want the firework show, but there’s not enough economic incentive to go through the effort and cost of arranging it if you only reap the private benefit (the en...
Alex KroegerA modest proposal: USDC v3
TLDRCentre’s USDC has become the second largest stablecoin in DeFi. While it has maintained its 1:1 peg with the US dollar well, its “blacklist” functionality comes with a heavy tax on users, amounting to, by my estimate, an extra $3.6M spent in transaction fees in December 2021 alone. I propose a new version of USDC that preserves what I argue is the most salient feature of the blacklist--the ability to freeze funds--in a way that eliminates blacklist-related gas costs for users, which can b...
Alex KroegerThe US government as a smart contract
A technical guide to on-chain governanceDisclaimer: this post and accompanying code is meant for educational purposes only. None of this is audited, and you should seek out audits before using contracts in production. There has been much ado about DAOs lately--they are being used to govern financial protocols, form modern day guilds, and crowdfund the sale of historical documents. But how do they work on-chain? This post will walk through an example of setting up governance using a variant of...
Alex KroegerDAOs and the Free-rider Problem
The free-rider problem arises when a good or service has private costs and non-excludable public benefits. A classic example is a firework show. Fireworks can be enjoyed by anyone in a particular area regardless of whether they pay for it (it’s hard to stop people from looking at the sky if they don’t have a ticket). Lots of folks may want the firework show, but there’s not enough economic incentive to go through the effort and cost of arranging it if you only reap the private benefit (the en...
Subscribe to Alex Kroeger
Subscribe to Alex Kroeger
Share Dialog
Share Dialog
<100 subscribers
<100 subscribers
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.
In this post we’ll walk through analyzing some data from a contract that is yet to be verified on Dune--Juicebox’s Terminal V1 contract that is being used by Constitution DAO.
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 ethereum.logs as topic1, topic2...
The first topic is almost always the “event signature,” an identifier for the event.
There are two ways to get this:
Grab it from an example event on a block explorer
Compute it yourself using the event information
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…)')
For the Pay event, it’s:
keccak256('Pay(uint256,uint256,address,uint256,string,address)')
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 topic3 and beneficiary will be in topic4. amount is concatenated with the other non-indexed parameters in the data field.
You’ll notice that the data fields (topic* and 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
For 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 (note and called). Since 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.
Looks great!
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. The 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 :)
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.
In this post we’ll walk through analyzing some data from a contract that is yet to be verified on Dune--Juicebox’s Terminal V1 contract that is being used by Constitution DAO.
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 ethereum.logs as topic1, topic2...
The first topic is almost always the “event signature,” an identifier for the event.
There are two ways to get this:
Grab it from an example event on a block explorer
Compute it yourself using the event information
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…)')
For the Pay event, it’s:
keccak256('Pay(uint256,uint256,address,uint256,string,address)')
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 topic3 and beneficiary will be in topic4. amount is concatenated with the other non-indexed parameters in the data field.
You’ll notice that the data fields (topic* and 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
For 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 (note and called). Since 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.
Looks great!
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. The 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 :)
No activity yet