Flipside World Cup Round of 8: Crosschain Defi Monitoring

    📝 Description of work → Image Source

    In this dashboard, we want to analyze and compare DeFi swapping activity across different chains such as Flow, Ethereum, Solana, Algorand, NEAR, and THORChain and different DEXs on each chain.

    This dashboard includes the following sections:

    1️⃣ → Introductions and methodology

    • Dashboard structure

    • What is a DEX?

    • Analyzed chains and methodology

    2️⃣ → Comparison of swapping activity across different chains

    1 - 1 → Introductions and methodology

    ▶️ Dashboard structure

    In this dashboard, swap activities among different chains have been analyzed first.

    • Metrics used for comparing different chains include swap volume, number of swaps, Number of swappers, Average swap volume size, Volume of swaps from/to stablecoins, Number of swaps from/to stablecoins, Number of swappers that swapped from/to stablecoins, and Average swap volume size of swaps from/to stablecoins

      \

    ▶️ What is a DEX?

    A decentralized exchange (DEX) is a peer-to-peer (P2P) marketplace that connects cryptocurrency buyers and sellers. In contrast to centralized exchanges (CEXs), decentralized platforms are non-custodial, meaning a user remains in control of their private keys when transacting on a DEX platform. In the absence of a central authority, DEXs employ smart contracts that self-execute under set conditions and record each transaction to the blockchain. These trustless, secure transactions represent an accelerating segment of the digital asset market, and are pioneering new financial products.

    Source: gemini.com

    2 - 1 → Analyzed chains and methodology


    > To deal with this bounty, we use ethereum.core, algorand.defi, solana.core, thorchain.core and flow.core schemas. In the following, we explain how to obtain each of the transactions related to Dex and how to calculate them for each chain.

    Data related to DEXs on Flow didn't exist in the current data.

    2 → Comparison of swapping activity across different chains


    • In this section, you can compare chains together using the “Chains_comparison“ drop-down list on the top of the dashboard, and you can change the time span of results and charts using the “Timespan“ drop-down list.
    • Also, you can change the time frame of charts using the “Time_Frame“ drop-down list.
    Loading...

    1 - 2 Swap volume in USD on selected chains, selected timespan, and selected time frame.

    Loading...

    2 - 2 Number of swaps on selected chains, selected timespan, and selected time frame.

    Loading...
    Loading...

    3 - 2 Number of swappers on selected chains, selected timespan, and selected time frame.

    4 - 2 Average swap volume size on selected chains, selected timespan, and selected time frame.

    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...

    5 - 2 Volume of swaps from stablecoins on selected chains, selected timespan, and selected time frame.

    6 - 2 Number of swaps from stablecoins on selected chains, selected timespan, and selected time frame.

    7 - 2 Number of swappers that swapped from stablecoins on selected chains, selected timespan, and selected time frame.

    8 - 2 Average swap volume size of swaps from stablecoins on selected chains, selected timespan, and selected time frame.

    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...

    9 - 2 Volume of swaps to stablecoins on selected chains, selected timespan, and selected time frame.

    10 - 2 Number of swaps to stablecoins on selected chains, selected timespan, and selected time frame.

    11 - 2 Number of swappers that swapped to stablecoins on selected chains, selected timespan, and selected time frame.

    12 - 2 Average swap volume size of swaps to stablecoins on selected chains, selected timespan, and selected time frame.

    Loading...
    Loading...
    Loading...
    Loading...
    Loading...
    Loading...

    13 - 2 Share of stablecoins (In %) from the volume of swaps from assets on selected chains, selected timespan, and selected time frame.

    14 - 2 Share of stablecoins (In %) from the volume of swaps to assets on selected chains, selected timespan, and selected time frame.

    Loading...
    Loading...
    db_img
    db_img
    db_img

    Defi Monitoring on Ethereum


    To find DEXs swap transactions on Ethereum, we use the ez_dex_swaps table. In this table, the PLATFORM field indicates the DEX on which the Swap was performed. DEXs we have reviewed for Ethereum include balancer, uniswap-v2, uniswap-v3, sushiswap and curve

    Base Query:

    SELECT BLOCK_TIMESTAMP, TX_HASH AS TX, PLATFORM AS DEX ,
    ORIGIN_FROM_ADDRESS AS SWAPPER FROM ethereum.core.ez_dex_swaps 
      WHERE AMOUNT_IN_USD IS NOT NULL AND AMOUNT_OUT_USD IS NOT NULL 
      AND AMOUNT_IN_USD != 0 AND AMOUNT_OUT_USD != 0
      AND SYMBOL_IN IS NOT NULL AND SYMBOL_OUT IS NOT NULL
      AND TOKEN_IN IS NOT NULL AND TOKEN_OUT IS NOT NULL
      AND EVENT_NAME IN('Swap', 'TokenExchange')
      AND AMOUNT_OUT_USD / AMOUNT_IN_USD BETWEEN 0.6 AND 1
    

    We used filter AMOUNT_OUT_USD / AMOUNT_IN_USD BETWEEN 0.6 AND 1 to get the list of swaps in Ethereum because some swap transactions have wrong values and we ignore these transactions because AMOUNT_IN_USD is always less than AMOUNT_OUT_USD (due to fee network etc.)

    We use the AMOUNT_IN_USD field for the volume of swaps in USD

    db_img
    db_img

    Defi Monitoring on Algorand


    To find DEXs swap transactions on Algorand, we use the fact_swap table. In this table, the SWAP_PROGRAM field indicates the DEX on which the Swap was performed. DEXs we have reviewed for Algorand include tinyman, wagmiswap, algofi, humbleswap, pactfi and fxdx

    Base Query:

    SELECT BLOCK_TIMESTAMP, SWAP_PROGRAM AS DEX, TX_GROUP_ID AS TX, 
    SWAPPER, SWAP_FROM_ASSET_ID AS FROM_ASSET_ID,
      	IFF(SWAP_FROM_ASSET_ID IN (SELECT ASSET_ID 
    FROM STABLECOINS_ON_Algorand), 'Stablecoin', 'Other')
     AS FROM_ASSET_TYPE, 
      	SWAP_FROM_AMOUNT AS FROM_ASSET_AMOUNT, SWAP_TO_ASSET_ID 
    AS TO_ASSET_ID,
      	IFF(SWAP_TO_ASSET_ID IN (SELECT ASSET_ID 
    FROM STABLECOINS_ON_Algorand), 'Stablecoin', 'Other') 
    AS TO_ASSET_TYPE, 
      	SWAP_TO_AMOUNT AS TO_ASSET_AMOUNT
      	FROM algorand.defi.fact_swap
    

    In this table, the value of SWAP_TO_AMOUNT is based on the token to which it has been swapped, so to show the amount of swaps in USD, we first use the ez_price_pool_balances table to obtain the daily price of each token, and then join with the query result.

    Defi Monitoring on Solana


    To find DEXs swap transactions on Solana, we use the fact_swap table. In this table, the SWAP_PROGRAM field indicates the DEX on which the Swap was performed. DEXs we have reviewed for Solana include jupiter aggregator v2, orca, raydium v4 and saber

    Base Query:

    SELECT BLOCK_TIMESTAMP, TX_ID AS TX, SWAP_PROGRAM AS DEX,
     SWAPPER, SWAP_FROM_MINT AS FROM_ASSET,
      IFF(SWAP_FROM_MINT IN (SELECT ADDRESS 
    FROM STABLECOINS_ON_SOLANA), 'Stablecoin', 'Other') 
    AS FROM_ASSET_TYPE, SWAP_FROM_AMOUNT AS FROM_ASSET_AMOUNT,  
      SWAP_TO_MINT AS TO_ASSET,
      IFF(SWAP_TO_MINT IN (SELECT ADDRESS 
    FROM STABLECOINS_ON_SOLANA), 'Stablecoin', 'Other') 
    AS TO_ASSET_TYPE,
      SWAP_TO_AMOUNT AS TO_ASSET_AMOUNT
      FROM solana.core.fact_swaps WHERE SUCCEEDED = 'TRUE' 
    

    In this table, the value of SWAP_TO_AMOUNT is based on the token to which it has been swapped, , so to show the amount of swaps in USD, we first use the fact_token_prices_hourly table to obtain the daily price of each token, and then join with the query result but we only have the price of 107 tokens in the fact_token_prices_hourly table, so to get the USD price of other tokens, we use the fact_swap table, for transactions that are swapped from stablecoins to other swap , then we get the average daily price of the tokens.

    db_img
    db_img

    Defi Monitoring on Flow


    To find DEXs swap transactions on Flow, we use the ez_swaps table. In this table, all swaps are related to BloctoSwap DEXs, and we did not find any other information about Flow DEXs.

    Base Query:

    SELECT BLOCK_TIMESTAMP, TX_ID AS TX ,'Blocto' AS DEX,
     TRADER AS SWAPPER, TOKEN_OUT_CONTRACT AS FROM_ASSET,
      IFF(TOKEN_OUT_CONTRACT IN (SELECT ADDRESS 
    FROM STABLECOINS_ON_Flow), 'Stablecoin', 'Other') 
    AS FROM_ASSET_TYPE,
      TOKEN_OUT_AMOUNT AS FROM_ASSET_AMOUNT, 
      TOKEN_IN_CONTRACT AS TO_ASSET, 
      IFF(TOKEN_IN_CONTRACT IN (SELECT ADDRESS
     FROM STABLECOINS_ON_Flow), 'Stablecoin', 'Other') AS TO_ASSET_TYPE,
      TOKEN_IN_AMOUNT AS TO_ASSET_AMOUNT,
      RANK() OVER(PARTITION BY TX ORDER BY SWAP_INDEX DESC) Rank
      FROM flow.core.ez_swaps
    

    In this table, the value of TOKEN_IN_AMOUNTis based on the token to which it has been swapped, so to show the amount of swaps in USD, we first use the fact_prices table to obtain the daily price of each token, and then join with the query result.

    Defi Monitoring on THORChain


    To find DEXs swap transactions on THORChain, we use the fact_swap table. In this table, the AFFILIATE_ADDRESS field indicates the DEX on which the Swap was performed. DEXs we have reviewed for THORChain include

    THORWallet, Defispot, DecentralFi and THORSwap / THORChain.

    Find THORChain DEXs:

    CASE 
        	WHEN AFFILIATE_ADDRESS 
    IN ('thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8', 'wr') 
    THEN 'THORWallet'
        	WHEN AFFILIATE_ADDRESS =
     'thor1zw97zhhgwy3u99rxpn7cyelj44y733ntgg30e8' 
    THEN 'Defispot'
        	WHEN AFFILIATE_ADDRESS = 
    'thor198d7hmxx9xptw8w3vswys30ss074selch3jw2p' THEN 'DecentralFi'
        	ELSE 'THORSwap / THORChain'
        END AS DEX,
    

    See Base Query

    We use the TO_AMOUNT_USDfield for the volume of swaps in USD

    db_img

    Defi Monitoring on Near


    To find DEXs swap transactions on Near, we use the ez_dex_swaps

    table. In this table, the PLATFORM field indicates the DEX on which the Swap was performed. DEXs we have reviewed for Near include Ref Finance Exchange and Jumbo Exchange

    Find Near DEXs:

    IFF(PLATFORM IN ('v1.jumbo_exchange.near', 
    'token.jumbo_exchange.near', 'fbrrr.jumbo_exchange.near'), 
    'Jumbo Exchange', 'Ref Finance Exchange') AS DEX, 
    

    See Base Query

    In ez_dex_swaps table, the value of AMOUNT_INis based on the token to which it has been swapped, , so to show the amount of swaps in USD, we first use the fact_prices table to obtain the daily price of each token, and then join with the query result but we only have the price of 13 tokens in the fact_prices table, so to get the USD price of other tokens, we use the ez_dex_swaps table, for transactions that are swapped from stablecoins to other swap , then we get the average daily price of the tokens.