with block as (
select
block_timestamp::date as date,
block_number
from ethereum.core.fact_blocks
where block_number >= (15088879 - 100000) --max(block_number) = 15088879
GROUP by 1, 2
)
,all_pools as (
select POOL_ADDRESS,
platform
from ethereum.core.dim_dex_liquidity_pools
where platform in ('sushiswap', 'uniswap-v2', 'uniswap-v3')
)
, tvl as (
select
balance_date::date as date_,
POOL_ADDRESS as Qualified_Pools,
CASE
WHEN platform = 'uniswap-v3' or platform = 'uniswap-v2' THEN 'uniswap'
ELSE 'sushiswap'
END as "main platform",
count(Qualified_Pools) as "Qualified pool Count",
sum(amount_usd) as Balance_in_usd
from ethereum.erc20_balances, all_pools
where user_address = POOL_ADDRESS
and balance_date = CURRENT_DATE - 1
and has_price = true
group by 1, 2, 3
order by 1
)
select b.block_number, a.Balance_in_usd
from tvl a join block b on a.date_ = b.date
where Balance_in_usd > 100000
group by 1,2