select

max(block_number) - 100000 as block

from ethereum.core.fact_blocks

),

uniswap_pools as (

select

pool_address,

token0,

token1

from ethereum.core.dim_dex_liquidity_pools

where platform = 'uniswap-v3' or platform = 'uniswap-v2'

),

uniswap_qualified_Pools as (

select

pool_address as pool,

sum(amount_usd) as pool_tvl

from ethereum.erc20_balances, uniswap_pools

where user_address = pool_address

and contract_address in (token0, token1)

and balance_date = current_date - 1

and amount_usd < 500000000

group by pool

having pool_tvl > 100000

select

pool_name,

sum(amount_in_usd) as volume

from ethereum.core.ez_dex_swaps

where contract_address in (select pool from uniswap_qualified_Pools)

and block_number >= (select block from block_last_100000)

group by pool_name

having volume is not null

order by volume desc