Day 7 (12 Days of Dune): Analyzing liquidity added and removed from USDC/WETH in Uniswap v2
Now that we understand swap usage, let's look at the other side - liquidity providers.
If you’ve made it this far, you’re officially halfway through! We’re starting from an “easy” level again, and will work our way up in difficulty till the last query this week.
We’ll be answering questions and covering more beginner-focused concepts during office hours at 2pm EST, so join us in the #12-days-of-dune discord channel. You’ll need to earn the course NFT and connect to the Dune guild.
Web3 Data Degens is a reader-supported publication. To receive new posts and support my work, consider becoming a free or paid subscriber.
Welcome to day 7!
You can find the full explanation below (premiering with a live watch party at 9 am EST today).
Here are the main concepts:
Liquidity providers have to deposit/remove liquidity in equal amounts (so 50 USDC and 50 dollars worth of WETH), and they get an ERC20 token representing their share of the pool.
Because liquidity is represented by an ERC20 token, we can check for the total supply by taking total mints and total burns for a pair over time. This is one of the most common query patterns you’ll see for finding the balance of any kind of token, so learn it well!
the erc20_ethereum.evt_Transfer table contains transfer events for every ERC20 token on the chain! So you can apply the total supply portion of the query to
This is also the first time you’re seeing how to create a series of dates. We need to create a series because there are some days without any actions - so for an accurate chart, we need to fill that date with 0. This is also the first time you’re seeing a FULL OUTER JOIN. You’ve already seen LEFT, INNER, and CROSS (cross is with just the comma in the router problem, query 3). You don’t lose any data on this join, but you need to “coalesce()” in basically every column. It looks ugly, but it is straightforward. Don’t worry about understanding all the code in this CTE, you’ll need to copy and paste it when necessary and then change the subquery for selecting a minimum start date!
Altogether you should get the charts below. The query is on Dune here.
Remember, if you found this question hard make sure to come to our office hours (2 PM EST) in our discord! Details are at the top of the email.
Tomorrow, we’ll be covering the question:
What is the percent concentration of liquidity sources (i.e. how much is owned by who) for the pair USCD/WETH?
As always, feel free to reach out to me here or on Twitter with any questions you might have.