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 ('uniswap')

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

and PLATFORM in ('uniswap-v3', 'uniswap-v2')

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

order by tvl DESC

),

top_10 as (

select symbol_in as name,

zeroifnull(sum(AMOUNT_IN_USD)) as swap_vol

from ethereum.core.ez_dex_swaps

where platform = 'uniswap-v2'

and tx_hash != '0x71cefe57d16b73792d6c1b7f176345ff81050a11b4e927b87f89afed79f64e33' -- Clean junk data

and CONTRACT_ADDRESS in (SELECT POOL_ADDRESS from qualified)

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

and name in ('USDC', 'WETH', 'WBTC', 'USDT', 'DAI')

group by name

UNION

select symbol_out as name,

zeroifnull(sum(AMOUNT_OUT_USD)) as swap_vol

from ethereum.core.ez_dex_swaps

where platform = 'uniswap-v2'

and tx_hash != '0x71cefe57d16b73792d6c1b7f176345ff81050a11b4e927b87f89afed79f64e33' -- Clean junk data

and CONTRACT_ADDRESS in (SELECT POOL_ADDRESS from qualified)

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

and name in ('USDC', 'WETH', 'WBTC', 'USDT', 'DAI')

group by name

UNION

select TOKEN0_SYMBOL as name,

zeroifnull(sum(abs(AMOUNT0_USD))) as swap_vol