case

when (symbols:token0 in ('USDT','USDC','DAI','FRAX','MIM') and symbols:token1 in ('USDT','USDC','DAI','FRAX','MIM')) then 'Stable-Stable'

when (symbols:token0 in ('USDT','USDC','DAI','FRAX','MIM') and symbols:token1 not in ('USDT','USDC','DAI','FRAX','MIM')

or

symbols:token1 in ('USDT','USDC','DAI','FRAX','MIM') and symbols:token0 not in ('USDT','USDC','DAI','FRAX','MIM')) then 'Stable-Volatile'

when (symbols:token0 not in ('USDT','USDC','DAI','FRAX','MIM') and symbols:token1 not in ('USDT','USDC','DAI','FRAX','MIM')) then 'Volatile-Volatile'

end AS Type,*

from arbitrum.defi.dim_dex_liquidity_pools where PLATFORM='uniswap-v3'),

arb1 AS (

select

CONTRACT_ADDRESS,

b.pool_name,

symbols:token0 AS token0,

symbols:token1 AS token1,

type,

sum(case when AMOUNT_IN_USD is not null then AMOUNT_IN_USD

when AMOUNT_OUT_USD is not null then AMOUNT_OUT_USD else 0 end ) AS Volume

from arbitrum.defi.ez_dex_swaps

inner join arb_pools b on CONTRACT_ADDRESS=pool_address

and date_trunc('month',block_timestamp) = '2024-06-01'

group by 1,2,3,4,5 order by 6 desc limit 200),

arb2 AS (

select

CONTRACT_ADDRESS,

Pool_name,

type,

token0,

token1,

CAST(SUBSTRING(pool_name,

CHARINDEX(' ', pool_name) + 1,

CHARINDEX(' ', pool_name, CHARINDEX(' ', pool_name) + 1) - CHARINDEX(' ', pool_name) - 1) AS INT)/1e4 AS Feepercent,

from arb1)

select

pool_name,