with pool_1 as (
select
block_timestamp::date as date,
case
when date > '2022-10-31' and date <'2022-11-08' then 'First_week'
when date > '2022-11-07' and date <'2022-11-15' then 'Second_week'
when date > '2022-11-14' and date <'2022-11-22' then 'Third_week'
else 'Fourth_week' end as Timespin,
tx_id,
pool_id,
count (tx_id) as TXs_1Week
from osmosis.core.fact_liquidity_provider_actions
where POOL_ID in ('833','837','840','634')
and action = 'pool_joined'
and BLOCK_TIMESTAMP::date > '2022-10-31' and BLOCK_TIMESTAMP::date < '2022-11-30'
and TX_STATUS = 'SUCCEEDED'
group by 1,2,3,4
)
select
b.date,
b.Timespin,
b.pool_id as pool,
count(distinct b.tx_id) as TXs,
count(distinct liquidity_provider_address) as Providers
from osmosis.core.fact_liquidity_provider_actions a
join pool_1 b on a.tx_id = b.Tx_id
group by 1,2,3
order by 1