Day 5 (12 Days of Dune): Identifying sources of swaps as contracts or EOAs

Because it's actually mostly the Router02 calling our pair, this isn't as easy as it sounds!

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 5!

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 just one new concept we add today - but it’s a difficult one. Let’s finally talk about the “traces” table. A trace (also called an “internal transaction”) is when a contract calls another contract or address in the middle of a transaction. Say you call the Uniswap Router02 to swap WETH to USDC. The Router02 has to call the WETH/USDC pair to exchange the tokens. In practice, it looks like this:

  

  thanks blocksec

• So if we check traces to see who called the pair, we’ll see that Router02 made the call. If we go up one more level, we’ll see that the sender (signer) made the call. In many cases, though, another contract (like an aggregator) calls the router. We want to combine those who called the pair directly or the router - that requires a self-join!

  

  cardinality of trace_address shown in red lol

• Self-join is when you join a table on itself; in our case, we want to join on lower cardinality traces where “from” that called the pair “to” (at 1) is now the “to” (at 0). Jackie explains it more in the video.

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.

Tomorrow, we’ll be covering the question:

We’re summing up our learnings this week! Can you combine MEV, routing, volumes, swappers, and/or sources if different ways to visually compare all USDC pairs in Uniswap v2?

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