Delamir-6014osm02 Compare Number of TXs and providers of pools over November
    Updated 2022-12-03
    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



    Run a query to Download Data