Method

Visualize the amount of WBTC swapped and count of swaps in the WBTC-WETH SushiSwap pool by day on April 1 - April 15, 2022.

we are going to analyze emitted swap events from the SushiSwap WBTC-WETHpool.

WBTC-WETH Pool:

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. Aggregating and Analyzing the data
4. Visualizing our findings

SELECT
   *
FROM
   ETHEREUM_CORE.FACT_EVENT_LOGS
WHERE
   block_timestamp >= '2022-04-01' AND  block_timestamp < '2022-04-16'
   AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58') -- WBTC-WETH pool on Sushiswap’s contract address
   AND event_name IN ('Swap')

Finding Swap Events

To find swaps in the WBTC-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 from April 1 - April 15, 2022
2. CONTRACT_ADDRESS - this is the contract where the event, in this case a swap, occurred. We will want to filter this for the WBTC-WETH pool address above.
3. EVENT_NAME - this is the name of the event emitted by the contract. Contracts can have multiple event types for the same contract address, so we will want to filter this for only Swap events.

Finding token details

Flipside has a DIM_DEX_LIQUIDITY_POOLS table, which contains details about different liquidity pools on Ethereum. We can query this table for the WBTC-WETH pool address to find details on the pool.

WITH pools AS (
SELECT 
pool_name, 
pool_address, 
token0, 
token1 
FROM ETHEREUM_CORE.DIM_DEX_LIQUIDITY_POOLS 
WHERE pool_address = LOWER('0xCEfF51756c56CeFFCA006cD410B03FFC46dd3a58')),
decimals AS (
   SELECT
       address,
       symbol,
       decimals
   FROM
       ETHEREUM_CORE.DIM_CONTRACTS
   WHERE
       address = (
           SELECT
               LOWER(token1)
           FROM
               pools
       )
       OR address = (
           SELECT
               LOWER(token0)
           FROM
               pools
       )
)
SELECT
   pool_name,
   pool_address,
   token0,
   token1,
   token0.symbol AS token0symbol,
   token1.symbol AS token1symbol,
   token0.decimals AS token0decimals,
   token1.decimals AS token1decimals
FROM
   pools
   LEFT JOIN decimals AS token0
   ON token0.address = token0
   LEFT JOIN decimals AS token1
   ON token1.address = token1

Aggregating and Analyzing the data

Now that we have the base data for our analysis, including all decimal adjusted swaps within the WBTC-WETH SushiSwap pool for April 1 - April 15, 2022, let’s aggregate our data and visualize our findings!

-- get details for relevant pool
WITH pools AS (
   SELECT
       pool_name,
       pool_address,
       token0,
       token1
   FROM
       ETHEREUM_CORE.DIM_DEX_LIQUIDITY_POOLS
   WHERE
       pool_address = LOWER('0xCEfF51756c56CeFFCA006cD410B03FFC46dd3a58')
),
-- get details for tokens in relevant pool
decimals AS (
   SELECT
       address,
       symbol,
       decimals
   FROM
       ETHEREUM_CORE.DIM_CONTRACTS
   WHERE
       address = (
           SELECT
               LOWER(token1)
           FROM
               pools
       )
       OR address = (
           SELECT
               LOWER(token0)
           FROM
               pools
       )
),
-- aggregate pool and token details
pool_token_details AS (
   SELECT
       pool_name,
       pool_address,
       token0,
       token1,
       token0.symbol AS token0symbol,
       token1.symbol AS token1symbol,
       token0.decimals AS token0decimals,
       token1.decimals AS token1decimals
   FROM
       pools
       LEFT JOIN decimals AS token0
       ON token0.address = token0
       LEFT JOIN decimals AS token1
       ON token1.address = token1
),

swaps AS (
   SELECT
       block_number,
       block_timestamp,
       tx_hash,
       event_index,
       contract_address,
       event_name,
       event_inputs,
       event_inputs :amount0In :: INTEGER AS amount0In,
       event_inputs :amount0Out :: INTEGER AS amount0Out,
       event_inputs :amount1In :: INTEGER AS amount1In,
       event_inputs :amount1Out :: INTEGER AS amount1Out,
       event_inputs :sender :: STRING AS sender,
       event_inputs :to :: STRING AS to_address
   FROM
       ETHEREUM_CORE.FACT_EVENT_LOGS
   WHERE
       block_timestamp >= '2022-04-01' AND  block_timestamp < '2022-04-16'
       AND event_name = ('Swap')
       AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
),
-- aggregate pool, token, and swap details
swaps_contract_details AS (
   SELECT
       block_number,
       block_timestamp,
       tx_hash,
       event_index,
       contract_address,
       amount0In,
       amount0Out,
       amount1In,
       amount1Out,
       sender,
       to_address,
       pool_name,
       pool_address,
       token0,
       token1,
       token0symbol,
       token1symbol,
       token0decimals,
       token1decimals
   FROM
       swaps
       LEFT JOIN pool_token_details
       ON contract_address = pool_address
),
-- transform amounts by respective token decimals
final_details AS (
   SELECT
       pool_name,
       pool_address,
       block_number,
       block_timestamp,
       tx_hash,
       amount0In / pow(
           10,
           token0decimals
       ) AS amount0In_ADJ,
       amount0Out / pow(
           10,
           token0decimals
       ) AS amount0Out_ADJ,
       amount1In / pow(
           10,
           token1decimals
       ) AS amount1In_ADJ,
       amount1Out / pow(
           10,
           token1decimals
       ) AS amount1Out_ADJ,
       token0symbol,
       token1symbol
   FROM
       swaps_contract_details
)
SELECT
   DATE_TRUNC(
       'day',
       block_timestamp
   ) AS DATE,
   COUNT(tx_hash) AS swap_count,
   SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS usdc_vol
FROM
   final_details
GROUP BY
   DATE
ORDER BY
   DATE DESC

Visualizing the Data

we will show WBTC volume in WBTC-WETH pool

Does this pool have more or less swap volume compared to WETH-USDC?

So we will compare the results with from WETH-USDC which explained in

Conclusion

From Cumulative bar chart we can say that amount of WBTC volume in the WBTC-WETH SushiSwap pool more than USDC volume in USDC-WETH pool on April 1 - April 15, 2022.