date_trunc('day', block_date) as dt,

pool_id,

sum(fees) as fees,

sum(fees_usd) as daily_fees_usd,

case when dt> current_date - 30 then '30days'

when dt > current_date - 60 then '60days'

else '90days' end as timeframe

from osmosis.core.fact_pool_fee_day

where dt>= current_date - 90 and fees_usd > 0

group by 1,2

order by dt desc),

pools as (select dt, count(distinct pool_id) as pool_available,

SUM(pool_available) OVER(ORDER BY dt asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_pools

from (

select

pool_id,

min(block_date) as dt

from osmosis.core.fact_pool_fee_day

group by 1)

group by dt),

fees2 as (select dt, sum(daily_fees_usd) as total_fees, timeframe

from fees

group by 1,3)

select a.dt, a.total_fees, a.timeframe, b.cumulative_pools

from fees2 a

left outer join pools b on a.dt = b.dt