Day 6 (12 Days of Dune): Clustering all USDC pairs in Uniswap by Usage
Let's put together all the queries we've done so far!
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 6!
You can find the full explanation below (premiering with a live watch party at 9 am EST today).
Here are the main concepts:
We wanted to get the total volume, total swaps, total MEV, total swappers, and total paths routed for every single USDC pair in Uniswap v2.
First we had to perform some CASE WHEN tricks to make sure everything was priced by token 0 (USDC). Many pairs have tokens that aren’t popular/aren’t in Dune’s prices.usd table, so this is an important step.
For routing, all we need to do to generalize is move the “contains_sequence” checks from the WHERE statement to the LEFT JOIN ON statement. This one will test your ability to think about join versus where statements visually
Takeaways: Comparing across all USDC pairs, MEV % is pretty evenly distributed. It's >40% of the total volume for volatile tokens like WETH, FRAX, and WBTC, while sitting around 16% for stablecoins like USDT. USDC/WETH sits at 45% MEV volume over the last 8 months, with $4.2b in volume. Few pairs come close to USDC/WETH regarding routing path dependence, which makes sense.
For you: even if you don’t try the query, play around by putting in different tokens. See what cool things you find and then post them on Twitter/LinkedIn and tag me! :)
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:
We’re restarting in difficulty starting with the liquidity provider side of pairs next week, focusing on liquidity stability.
What is the total liquidity added or removed from USDC/WETH pair over time?
As always, feel free to reach out to me here or on Twitter with any questions you might have.