select POOL_ADDRESS as addy--, token0 , token1

from ethereum.core.dim_dex_liquidity_pools

where platform = 'sushiswap'),-- 'uniswap-v3' or 'uniswap-v2 )

uni2pools as (

select POOL_ADDRESS as addy --, token0 , token1

from ethereum.core.dim_dex_liquidity_pools

where platform = 'uniswap-v2'

and pool_name not like 'SAITAMA-WETH UNI-V2 LP'), -- saitama - weth pool had unsual amounts in usd so excluded

uni3pools as (

select POOL_ADDRESS as addy --, token0 , token1

from ethereum.core.dim_dex_liquidity_pools

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

blocks as (select

BLOCK_NUMBER as bl

from ethereum.core.fact_blocks

where BLOCK_TIMESTAMP <= '2022-06-30'

order by BLOCK_TIMESTAMP desc

limit 100000),

qu2 as (select sum(amount_usd) as tvl ,user_address

from flipside_prod_db.ethereum.erc20_balances

where LABEL_SUBTYPE = 'pool'

and user_address in (select addy from uni2pools)

and balance_date = '2022-06-30'

group by 2

having tvl >= 100000

order by 1 desc

),

vols as (

SELECT count (tx_hash) as txs, BLOCK_NUMBER, POOL_NAME, avg (AMOUNT_OUT_USD+AMOUNT_IN_USD) as v

from ethereum.core.ez_dex_swaps