SELECT POOL_ADDRESS, POOL_NAME, sum(zeroifnull(b.amount_usd)) as tvl

from ethereum.core.dim_dex_liquidity_pools a

join flipside_prod_db.ethereum.erc20_balances b on a.POOL_ADDRESS = b.USER_ADDRESS and b.balance_date = CURRENT_DATE - 5 and b.LABEL in ('sushiswap')

where pool_address not in ('0xf4bfe9b4ef01f27920e490cea87fe2642a8da18d', '0x9cbfb60a09a9a33a10312da0f39977cbdb7fde23') -- clean junk

and PLATFORM in ('sushiswap')

GROUP by POOL_ADDRESS, POOL_NAME having tvl >= 100000 -- above $100K

order by tvl DESC

),

top_10 as (

select pool_name,

zeroifnull(sum(AMOUNT_IN_USD)) as swap_vol

from ethereum.core.ez_dex_swaps

where block_timestamp::date >= CURRENT_DATE() - 90 and platform = 'sushiswap'

and tx_hash != '0x71cefe57d16b73792d6c1b7f176345ff81050a11b4e927b87f89afed79f64e33'

and CONTRACT_ADDRESS in (SELECT POOL_ADDRESS from qualified)

and BLOCK_NUMBER >= (SELECT max(BLOCK_NUMBER) - 100000 from ethereum.core.ez_dex_swaps)

group by pool_name

order by swap_vol DESC

limit 10

select * from top_10