Nenye

A Guide to Blockchain Analytics 2: Analyzing Token Swap data using Flipside

Alokam Chinenye Augusta's photo
Alokam Chinenye Augusta
·

11 min read

A Guide to Blockchain Analytics 2: Analyzing Token Swap data using Flipside

Table of contents

In the previous article, we learned about Blockchain Analytics as a new field in Blockchain technology. We saw how data is created on the Blockchain, the sources of the data, and how the data can be extracted and cleaned for analysis. We further explored the tools we can use to analyze the data and the normal analysis operations that can be carried out on Blockchain data.

This article will demonstrate all of this by performing a market analysis using blockchain data. Our data source is Flipside, and we perform operations like swap events, token details, manipulating JSON format, and aggregating the data.

In the demonstration, we will find the swap volume and swap count of the Wrapped BTC — Wrapped ETH (henceforth denoted as WBTC-WETH) pool in Sushiswap.

Let's get started with understanding what all these mean.

Terminologies used in this article

You may need to understand these terminologies to understand what is happening.

Token swapping

A token swap refers to exchanging one token for another on a decentralized exchange (DEX) like Sushi.

Swap events

A swap event is an activity where one possession is exchanged for another of the same value. Token swap events refer to events where one token is swapped for another.

Swap volume

This refers to the total value of tokens swapped in a specific trading pair. In this case, the volume of WBTC and WETH was changed in the Sushi WBTC-WETH pool.

Swap count

This refers to the number of times a trading pair, such as WBTC and WETH, has been traded on a decentralized exchange (DEX) like Sushi.

Wrapped Bitcoin (WBTC)

WBTC is an ERC-20 token representing the value of 1 Bitcoin. It allows users to use Bitcoin on Ethereum-based platforms such as Sushi.

Wrapped Ethereum (WETH)

WETH is an ERC-20 token representing the value of 1 Ether. It allows users to use Ether on Ethereum-based platforms such as Sushi.

Contract Address

The contract address of the WBTC-WETH pool on Sushi is a unique address on the Ethereum blockchain where the trading of WBTC and WETH takes place. This address can be found on the Sushi website or a blockchain explorer like Etherscan. The contract address would help us filter to the specified pool we are looking at in Sushi. The address used in this project is:

0xceff51756c56ceffca006cd410b03ffc46dd3a58

Project Procedure

For the project, we will look at the number and volume of swaps for the WBTC-WETH pool on the Sushi from April 1 - April 15, 2022, after which we will visualize our findings with the ethereum.core table.

Visualization of our target solution.

The project is broken down into these sub-tasks:

  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. Visualize your findings.

1. Finding Swap Events

To find swaps in the WBTC-WETH Sushiswap pool, we will need three key things:

  • BLOCK_TIMESTAMP: This is the timestamp of when the transactions occurred. This project's block timestamp is from the 1st to the 15th April 2022.

  • CONTRACT_ADDRESS: This is the contract where the event occurred, a swap. We will filter for the WBTC-WETH pool address above.

  • EVENT_NAME: This is the name of the event emitted by the contract. A contract can have multiple event types occurring in the same address; we will have to filter for Swap events.

The elements of a token swap described above are represented in a query like this:

SELECT 
            *
FROM 
        ethereum.core.fact_event_logs
WHERE
        block_timestamp BETWEEN '2022-04-01' AND '2022-04-15'
        AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
        AND event_name IN ('Swap')

When we run this query, there is an EVENT_INPUTS Column in the table that gives us transaction details in JSON format. It tells us how much the sender sent to the receiver and how much the receiver received in the other token's equivalent.

The transaction details look like this:

{
    "amount0In":8820223,
    "amount0Out":0,
    "amount1In":0,
    "amount1Out":1188620925799969273,
    "sender":"0x1111111254fb6c44bac0bed2854e76f90643097d",
    "to": "0x1111111254fb6c44bac0bed2854e76f90643097d"
}

The next task involves determining which token has amount0 and which token has amount1, to understand the swap dynamics.

2. Finding token details

Flipside has a DIM_DEX_LIQUIDITY_POOLS table, which contains details of different liquidity pools on Ethereum. We can query this table to get the WBTC-WETH pool address and find details of the pool like this:

SELECT
      pool_name,
      pool_address,
      token0,
      token1
  FROM
      ethereum.core.dim_dex_liquidity_pools
  WHERE
      pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')

From the above query, we intend to get the pool name, pool address, and contract address for token0 and token1. The output will look like this:

Next, we will figure out the symbol for each address, along with the number of decimals required to transform amounts. If we go back to our amounts in the JSON object output, we will notice that the different amounts are long numbers.

{
    "amount0In":8820223,
    "amount0Out":0,
    "amount1In":0,
    "amount1Out":1188620925799969273,
    "sender":"0x1111111254fb6c44bac0bed2854e76f90643097d",
    "to": "0x1111111254fb6c44bac0bed2854e76f90643097d"
}

In this example, 8820223 = 0.0882 WBTC and 1188620925799969273 = 1.1886 WETH.

We will need the dim_contracts table to find details on the respective tokens to transform these amounts to the correct values. We will also find the token symbol in this table. We will use a Common Table Expression (CTE) to filter the contracts table for token0 and token1.

WITH pools AS (
   SELECT
       pool_name,
       pool_address,
       token0,
       token1
   FROM
       ETHEREUM.CORE.DIM_DEX_LIQUIDITY_POOLS
   WHERE
       pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
)
SELECT
   address,
   symbol,
   decimals
FROM
   ETHEREUM.CORE.DIM_CONTRACTS
WHERE
   address = (
       SELECT
           LOWER(token1)
       FROM
           pools
   )
   OR address = (
       SELECT
           LOWER(token0)
       FROM
           pools
   )

The output will look like this:

We have the address, symbol, and decimals for the token0 and token1. Let's combine all the information into one query for our pool by adding some data to our CTE.

--details for the relevant pool
WITH pools AS (
    SELECT 
        pool_name, 
        pool_address, 
        token0, 
        token1 
    FROM
        ETHEREUM.CORE.DIM_DEX_LIQUIDITY_POOLS 
    WHERE 
        pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
),
--details for tokens in the 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
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

We have all the details needed for the tokens in our pool. From the result, the WBTC token uses 8 places decimal transformation, while the WETH token uses 18 decimal places. Let's go to the next thing, working with JSON data.

3. Working with JSON Objects

To correctly analyze swaps in this pool, we will need to pull specific fields out of the EVENT_INPUTS column. This column stores information about the specification in JSON object format. To work with this data format, we can use this generic formula:

<COLUMN_NAME>:<FIELD_NAME>::<FIELD_FORMAT> as <FIELD_NAME>

We would use this formula to add some columns to our Swap Events.

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 BETWEEN '2022-04-01' AND '2022-04-15'
   AND event_name = ('Swap')
   AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')

Putting the pieces together

We have successfully figured out all the necessary pool details, token details, and swap details. Next, we will put all these together in preparation for analysis by specifying the columns that we want from the previous query

Note we need to adjust the amount columns by each token's respective decimal. This can be achieved using the following formula:

<AMOUNT_COLUMN> / POW(10, DECIMALS)

The POW() takes a base of 10 and an exponent of the specified token decimal, e.g., token0 decimal is 8; therefore, 10^8 would be used to divide the amount.

Let's update the SQL query with a CTE that aggregates the swap, token pool details and another CTE that adjusts the amounts to their respective decimals.

-- details for the relevant pool
WITH pools AS (
   SELECT
       pool_name,
       pool_address,
       token0,
       token1
   FROM
       ETHEREUM.CORE.DIM_DEX_LIQUIDITY_POOLS
   WHERE
       pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
),
-- details for tokens in the 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
),
-- find swaps for relevant pool between April 1st to April 15th
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_timestam BETWEEN '2022-04-01' AND '2022-04-15'
       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
),
-- change amounts using 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
)
-- run the entire query 
SELECT
   *
FROM
   final_details

From the above query, we queried and got the following information:

  • Swap events within the WBTC-WETH pool from April 1 - April 15, 2022.

  • Token contract and pool details for the tokens in the WBTC-WETH pool.

  • Manipulated embedded data in a JSON object

  • Transformed decimal token amounts.

We now have the base query for our analysis; let's analyze our data and visualize our findings.

4. Analyzing the Data

From our instruction, we are to visualize the amount of WBTC swapped and the count of swaps by day in the WBTC-WETH pool. To achieve this, we will need to find the daily count of trades and aggregate the WBTC traded in and out of the pool. We first have to determine the direction of each transaction; since we know which token is token0 and which is token1, we can refer to the amounts to understand the trade direction. For this case, token0 is WBTC and token1 is WETH.

We can easily determine the direction of trade by looking at the amount0In_ADJ amount1In_ADJ columns. If amount0In_ADJ is greater than 0, the user swapped from WBTC to WETH, and they amount1Out_ADJ will be greater than 0 and vice versa.

With this information, we can find daily volume and trade count.

Sum of Volume

We are interested in WBTC volume, so we would be looking at the amount of WBTC in and out of the pool. Remember, WBTC is token0.

SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS TOTAL_WBTC_VOL

Trade Count

We can find the trade count using the number of transaction hashes in our swaps query.

COUNT(tx_hash) as swap_count

Aggregating the data

We will use the DATE_TRUNC function to get the data by day.

DATE_TRUNC('day', block_timestamp) as DATE

Now we know what data we are aggregating, let's add it to our base query. W can not put all these together in our previous query.

-- run the entire query 
SELECT
        DATE_TRUNC('day', block_timestamp) as DATE,
        COUNT(tx_hash) as swap_count,
        SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS TOTAL_WBTC_VOL
FROM
   final_details
GROUP BY
        1
ORDER BY 
        1 DESC

The above query would return the number of swap counts and total volume of WBTC in the WBTC-WETH pool for Sushi from April 1 - April 15, 2022.

5. Visualizing the data

We now have all our data; let's visualize our findings.

Step 1: Once our query is complete, click on 'New Chart' to begin visualizing

Step 2: Choose your chart design and plot your data points

Step 3: Change settings to make your visualization easier to read and understand by adding column names for all axis.

Step 4: Add your visual to the dashboard and add text to explain your query, chart, and any finds in the data! Check out mine here.

Conclusion

In conclusion, by analyzing data from Flipside, we could determine the swap volume and swap count of Wrapped Bitcoin (WBTC) in the WBTC-WETH pool in Sushi. Our analysis showed a high activity level in this trading pair, indicating significant interest in using Wrapped Bitcoin (WBTC) and Wrapped Ethereum (WETH) for token swaps in the Sushi platform.

We can also note that the use of data from Flipside allowed for a comprehensive analysis of the trading activity in the WBTC-WETH pool on Sushi. This highlights the utility of data analytics tools in understanding the behavior and trends in the decentralized finance (DeFi) ecosystem.

Overall, this analysis provides valuable insights into the usage and popularity of token swapping in the DeFi space and the utility of data analytics in understanding the activity on decentralized exchanges like Sushi.

I would love to connect with you on LinkedIn | Twitter | Github | Portfolio

Blockchaindata analysisEthereumBitcoindata