Methodology

Using FlipsideCrypto Data tables from Osmosis.

• With osmosis.core.dim_liquidity_pools we can know the amount of pools with count(pool_id), also the assets array will show us the amount of assets a pool have and to know the exactly amount of assets we’ll use a simple case with the following algorithm:
  • If assets[2]:asset_address (Third Asset Slot) is NULL that means the Pool have only 2 assets.
  • If assets[X]:asset_address is Null and assets[X-1]:asset_address is not Null that means the Pool have only X assets.
    • We need to look at the previous value also, to not include previous pools.
    • The Maximum Amount of Assets a pool have is 8
    • This Algorithm is to group the Pools by Amount of Assets.
  • If we join this table with osmosis.core.fact_liquidity_provider_actions using pool_id we can know exactly the amount of active pools, because there is a significant amount of pools without a single LP Activity.
    • osmosis.core.fact_liquidity_provider_actions has pool_id as array so we need to use pool_id[0] to Join
• How to Name Pools?
  • First We need to use osmosis.core.dim_liquidity_pools and then do the following algorithm (Reemplaze X with a number from 1-8):
    • case when AX.Project_name is null then assets[X-1]:asset_address else AX.project_name end as "Asset X" Basically we need to do X left joins with osmosis.core.dim_labels using AX tag to difference each Value

      • left join osmosis.core.dim_labels AX on assets[X-1]:asset_address=AX.Address Left Join Example

      • Because sometimes project_name can be NULL (unlabeled Tokens) we use assets[X-1]:asset_address To give the address if the value is not null then we give the name of the Token.

      • Also remember to keep the pool_id to identify the pools.

  • Second We need to use our both previous algorithm (The one to know how many assets have a pool & the one to give a name to each asset) after joining both using pool_id we do the final algorithm.
    • Case when "LP Type"='2 Assets LP' then concat("Asset 1",'-',"Asset 2")
    • …
    • when "LP Type"='8 Assets LP' then concat("Asset 1",'-',"Asset 2",'-',"Asset 3",'-',"Asset 4",'-',"Asset 5",'-',"Asset 6",'-',"Asset 7",'-',"Asset 8")
    • So in this case we use LP Type Identifier to know exactly the amount of assets the LP has, and then do a concat between each Asset and finally we get our Pool Name
• With osmosis.core.fact_liquidity_provider_actions we can get the rest of data from LPs:
  • action To know if the activity is for joining or leaving.
  • tx_id To know the amount of transactions.
  • liquidity_provider_address To know the amount of users
  • To get the $USD Amount we need to use sum(amount/pow(10,decimal)*"USDPRICE") Where “USDPRICE” comes from the avg(price) from osmosis.core.ez_prices get in other CTE and finally join both tables on Currency and Date.
• Finally osmosis.core.fact_swaps to get the Swaps using:
  • tx_id for the Swaps transactions
  • trader for the Swappers.

Summary

After Looking at the Recent data of Osmosis Liquidity Pools the best LPs by performance are:

• OSMO↔USDC: This is the Safest Pool for the Liquidity Providers, because people that use Osmosis to trade their $OSMO to $USD will always use this Pool, because is faster and cheaper than doing in other DEX or Blockchain.

• OSMO↔ATOM: This is the Most Popular Pool in Osmosis, like the previous Pool both are the best and safest pools for Liquidity Providers.

• OSMO↔EVMOS: Although the total Liquidity and Swap Activity is lower than the other pools, the APR is one of the highest and that’s the reason the amount of joining users is the highest.

• OSMO↔TORI: Because this LP pays in $TORI instead of $OSMO, a lot of users were joining this pool but when February 10 arrives and the pool stop paying in $TORI the activity could change so is one important pool to keep an eye.

  Dashboard Made by Popex404, Twitter Link Here

🔎Leaving Analysis🔍

Users and Transaction Activity seems to be in a range of 300-800 Transactions/Users Leaving Daily.

Meanwhile LPs $USD Withdraws seems to be more bigger on January 2023.

• Every Day the top pools like ATOM/USDC/EVMOS-OSMO are always present.
• OSMO-SEASY Pool suffers the biggest $USD Withdraw during the FTX Crash (November 10) with 3.8M $USD and then 1.28M in December 27

🔮Welcome to the Osmosis Zone🔮

• Osmosis is a Layer-1 Blockchain built on the Cosmos framework, operates a decentralized exchange (DEX) and the $OSMO is the native token which serves as governance and staking for users.
  • DEX: A peer-to-peer market where users can buy and sell assets (Crypto-tokens) without giving their private keys to an intermediate.
• Osmosis is a DEX blockchain, and you can see that immediately in their website, the first thing you see when you enter is the option to swap your assets.

Activity and Users are the same in these pair, (pool_joined,lp_tokens_minted) and (pool_exited,lp_tokens_burned)

📊General Metrics from Liquidity Pools in Osmosis (Since Genesis)📊

📊General Metrics from Liquidity Pools in Osmosis Since November 2022📊

📊Liquidity Pool Joining Activity Since 2022 📊

🔎Joining Analysis🔍

Users and Transaction Activity seems to be in a downtrend since November and Finally in December 31, starts a low uptrending which ends in January 16.

Meanwhile LPs $USD Deposits seems to be also in a downtrend, with some spikes between Days.

• WETH-OSMOS received a 4.8M $USD deposit in November 1
• USDC-OSMOS received a 2.6M $USD deposit in December 24
• ATOM-OSMOS received a 3.0 $USD deposit in January 16.

By far the best pools by Joining Activity are:

• USDC-OSMO having the best $USD Deposit and Top 3 in both Users and Transactions this is the safest Pool to Enter in Osmosis, because users only needs to be alert about $OSMO.
  • Also in Osmosis Website is the #2 Pool in Past 7 days FEES
• USDC-ATOM having Third Place in $USD Deposit and Fourth Place in Users and Transactions this is the most popular Pool in Osmosis, having both Osmosis and Cosmos Native tokens.
• OSMOS-EVMOS having Five Place in $USD Deposit and First Place in both users and transactions, this is one of the pool with the highest APR as we can see in Osmosis

📊Liquidity Pool Leaving Activity Since 2022 📊

🔎Swap Analysis🔍

Swap Activity starts downtrending after the FTX Crash but recently in January the amount of Swaps & Swappers have increase again

• Daily, the amount of Swaps made in OSMO-ATOM is bigger than OSMO-USDC.
• Behind of these LPs is OSMO-EVMOS LPs having a considerable amount of swaps.
• And in 4th Place is the OSMO-TORI Pool, this Liquidity Pool pays in $TORI an these rewards will stop in February 10 (120 since their Tweet ).

📊Liquidity Pool Activity Table 📊

Liquidity Pools Swap Activity Since November 2022

There is a notable Downtrending in Activity and Users since November 2022