Day 9 (12 Days of Dune): Calculating the percent of USDC reserves in USDC/WETH pair versus all other pairs

Figuring out reserve concentration across pairs gives us an idea of how important this pair is relative to others

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.

Welcome to day 9!

You can find the full explanation below (premiering with a live watch party at 9 am EST today).

Here are the main concepts:

• While we can check LP positions by tracking the balances of the LP token, the underlying reserves are always changing due to swaps. So the amount0 and amount1 at the time of mint/burn liquidity changes almost as soon as the event occurs.

• You could use the “erc20_ethereum.evt_Transfer” table to track USDC and WETH balances separately over time and then join them together. However, there is an easier method!

• if you looked deeper at the contract, you’d find the Sync() event is called by _update() in every key function (minting, swapping, etc)

  

  see the code on Etherscan

• So, we can get the most recent reserves of all USDC and WETH pairs using some row_number() window function magic - and then we have all our data!

• We finish with some UNION ALL magic to get two nice rows, to save users from scrolling horizontally in the table (which is a bad dashboard experience generally).

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 total liquidity and slippage of USDC/WETH? Slippage is the difference between actual swap rate and quoted swap rate - one of the most important DEX concepts.

As always, feel free to reach out to me here or on Twitter with any questions you might have.