select block_timestamp, value as pool1

from osmosis.core.fact_swaps , table(flatten(input => pool_ids))

),

new_pools_since_may as (

select *, value as pool

from osmosis.core.fact_swaps , table(flatten(input => pool_ids))

where block_timestamp >= '2022-05-01'

and (select min(block_timestamp) from allpools where pool1=value) >= '2022-05-01'

select date(block_timestamp) as day, count(distinct pool) as no_of_pools,

sum(no_of_pools) over (order by day) as cumulative_pools_count

from new_pools_since_may

where (select date(min(block_timestamp)) from allpools where pool1=pool) = date(block_timestamp)

group by day order by day desc