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
▶️ 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
\
Then, DEXs related to each chain have been analyzed in separate dashboards.
▶️ 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.
-
In this dashboard, we compared and analyzed DeFi-related swap activity across chains and we only compared chains against each other. In order to better understand the activity of each DeFi on each chain, as well as to analyze and verify the token pairs on each chain, a separate dashboard has been created for each of the chains, which provides a deeper look at the DeFis and token pairs on each chain.
-
All queries in this dashboard are set to run every 24 hours
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.
![db_img](https://uploads.flipsidecrypto.com/product/images/q2Ft7LPghwBi.png)
![db_img](https://uploads.flipsidecrypto.com/product/images/cLz7SBG0mQeD.jpeg)
![db_img](https://uploads.flipsidecrypto.com/product/images/h2_Xhda-CSLi.png)
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](https://uploads.flipsidecrypto.com/product/images/l7whxS2wdJiG.png)
![db_img](https://uploads.flipsidecrypto.com/product/images/N6mI2qUhV4aH.png)
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](https://uploads.flipsidecrypto.com/product/images/ftyvHLBmB96I.png)
![db_img](https://uploads.flipsidecrypto.com/product/images/o_SN65iSXJv8.png)
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_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_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,
We use the TO_AMOUNT_USD
field for the volume of swaps in USD
![db_img](https://uploads.flipsidecrypto.com/product/images/wEJWqUzmN6_g.png)
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,
In ez_dex_swaps table, the value of AMOUNT_IN
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_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.