Day 3 (12 Days of Dune): Finding pair reliance in Uniswap Swap Routing
Sometimes you have to hop through a few pairs to get from token A to token B. USDC/WETH sits in a the middle of a lot of those - but which paths are most popular?
(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 3!
You can find the full explanation below (premiering with a live watch party at 9 am EST today).
Here are the main concepts:
Router02 is a contract that helps route swaps efficiently across all pairs. Some swaps are direct (single pair and done), and some have to go through a few hops (a few different pairs and then done). The actual route (swap) path is calculated off-chain by Uniswap, and passed in as an input parameter (“path” column).
We check how many pairs were routed through by taking the “cardinality()” (length) of the “path” array column. We only keep the ones with cardinality > 2 (so at least one hop).
Here’s an example swap through the router that had to go through WETH/USDC. The output token (0x2b59…, HEX) has an existing pair with USDC (USDC/HEX). The input defines exactly what token is swapped at each step…
And we can see from the transaction that the HEX-USDC pair was called after the USDC-WETH pair.
Now to actually figure out which swaps used the USDC/WETH pair, we have to check for either “USDC, WETH” or “WETH, USDC” sequences in the path variable array. To do this we use “contains_sequence()” for either the first pattern or the second pattern. The function checks for an array sequence, so we have to wrap the addresses like so:
array[token0, token1]Lastly, we want to make examples of swaps on each path easy to find, so we use “arbitrary()” to pull a random transaction hash for a given swap path (like WETH/HEX) and place it in an href (meaning a hyperlink like this)
We use the new duneSQL query-a-query feature here, where we reference one query that contains all block explorers mapped to chains inside of our main query so that we can easily get the right URL. You can query any existing duneSQL query, so that your own code becomes more composable with others on the platform.
Altogether, we get the below chart as the answer. 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.
An aside on routers
There are many routers now, and it has gotten especially competitive at the aggregator level for both erc20s and NFTs (Uniswap, 0x, Opensea, and more all have their own routing methods and contracts). The newest edition is the UniversalRouter by Uniswap - I made a short query/data dive into it yesterday. It’s only a week old, so there isn’t enough data to play with yet. But definitely keep an eye on it!
Tomorrow, we’ll be covering the question:
Can you identify organic volume by removing bot swaps (arbitrage/mev) from pair swaps?
As always, feel free to reach out to me here or on Twitter with any questions you might have.
Hi Andrew, I am a beginner of SQL. Do you have any resources recommended for the "concat, left join function/querying basics. Cheers.