<aside> 💡 Welcome aboard! You’ve landed on one of our evergreen education bounties - open for everyone at anytime.

</aside>

This particular SQL guide focuses on the new ethereum.core tables. We take you on a step by step journey to look at the number of swaps and volume of swaps for the USDC-WETH pool on Sushiswap.

At the end of this guide, we’ve included a challenge for you to apply what you’ve learnt to compare swap volume and count for the WBTC-WETH pool on Sushiswap. The estimated time to go through this guide is 30 minutes - 1 hour.

Here’s what we’ll be doing:

Visualize the amount of USDC swapped and count of swaps in the USDC-WETH SushiSwap pool by day in the past 7 days using the ez_decoded_event_logs table. In this walk through, we are going to analyze emitted swap events from the SushiSwap USDC-WETH pool.

USDC-WETH Pool: https://etherscan.io/address/0x397FF1542f962076d0BFE58eA045FfA2d347ACa0#tokentxns

In order to complete this task using the event logs, we will need to explore a few concepts:

  1. Finding swap events for the relevant pool
  2. Finding token details for relevant tokens
  3. Working with JSON object columns
  4. Aggregating the data
  5. Visualizing our findings

Solution Example:

https://lh4.googleusercontent.com/mBOlVpkBNVLXvEz_2q51B0AmWvfELQpAYGRgCOplkJViZGQ4DcWuvHEY2-Q6q9DPk78D1Hla_2zRz_E95nvKYf1FXnV0NGBLCAAeu89CQuww3rIl7ZAyQlBFwvwvtzbRsrdpp3F2

Finding Swap Events

To find swaps in the USDC-WETH Sushi Pool, we will need to filter for three things:

  1. BLOCK_TIMESTAMP - this is when the transaction occurred. This is a rather large table, so we will want to make sure we filter for the last 7 days first.