Day 2 (12 Days of Dune): Finding total volume and swap frequency for Uniswap v2 (USDC/WETH)
Covering how to decode raw token amounts from swaps into USD volume totals.
(if you missed the original course announcement, read that first)
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 2!
You can find the full explanation below (premiering with a live watch party at 9 am EST today).
Here are the main concepts:
There is a “swap()” function and a “Swap()” event on the pair contract. However, the function call is pre-swap (meaning price has not yet been determined, only quoted) versus the event is emitted post-swap (meaning final prices and amounts in and out are recorded). In our case, using either is fine because we calculate based on the amount out - however, for other contracts you must be careful of what user/action state is represented by the function/event.
In the first day’s query, we utilized the tokens.erc20 table to get the token symbol. In this one, we get the symbol as well as the decimals. Ethereum doesn’t have the concept of decimals, instead, it pads a certain number of zeroes (decided upon when the erc20 contract is deployed). The standard is 18 decimals, but some like USDC have 6 decimals instead. We use decimals to convert raw token amounts from the event to actual amounts.
Joining on the prices.usd table gives us the USD amount value of the trade at that point in time. That table stores prices in minutes, so you have to date_trunc(‘minute’, evt_block_time) to get accurate prices - otherwise, swaps will end up missing price data.
We use CTEs to better organize the queries. Nested CTEs make the logic easier to follow for longer queries. A CTE is essentially just storing a query in a “variable”, and is for the most part no different from a subquery.
We use GROUP BY to get aggregated swap data over time. This one is very basic since we’re only grouping by one column (time of trade).
We can compare our results to the spellbook dex.trades table for Uniswap v2, and see that the values match up 1:1 because we’ve calculated values the same way (chart below)
Altogether, we get the below chart as the answer. The query is on Dune here.
Remember, if you found it 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:
Can you show the top routing paths that use USDC/WETH (i.e. how many times does a swap pass through this pair)? Use UniswapV2Router02 for this problem.
As always, feel free to reach out to me here or on Twitter with any questions you might have.