ramishoowUntitled Query
    Updated 2022-10-19
    with ramishow as( select count(distinct trunc(block_timestamp, 'day')) as Activity, tx_from as user, case
    when Activity >= 60 then 'Active users' when Activity >= 30 and Activity < 60 then 'Casual users'
    when Activity < 30 then 'Not active users' end as situation from osmosis.core.fact_transactions where block_timestamp > CURRENT_DATE - 90
    and TX_STATUS = 'SUCCEEDED' group by 2 order by 1 desc), active as (select user, Activity from ramishow
    where situation = 'Active users'), Lp as (SELECT trunc(BLOCK_TIMESTAMP, 'day') as date, count(DISTINCT TX_ID) as LP
    from osmosis.core.fact_liquidity_provider_actions where ACTION = 'pool_joined' and TX_STATUS = 'SUCCEEDED'
    and LIQUIDITY_PROVIDER_ADDRESS in (SELECT user from active) and BLOCK_TIMESTAMP > CURRENT_DATE - 90
    group by 1 order by 2 desc), swap as(select trunc(BLOCK_TIMESTAMP, 'day') as date, count(distinct TX_ID) as Swap
    --group by 1 order by 2 desc), swap as(select trunc(BLOCK_TIMESTAMP, 'day') as date, count(distinct TX_ID) as Swap
    --ramishow
    from osmosis.core.fact_swaps where TX_STATUS = 'SUCCEEDED' and TRADER in (SELECT user from active)
    and BLOCK_TIMESTAMP > CURRENT_DATE - 90 group by 1 order by 2 desc), vote as (select trunc(BLOCK_TIMESTAMP, 'day') as date, count(distinct TX_ID) as vote
    from osmosis.core.fact_governance_votes where TX_STATUS = 'SUCCEEDED' and voter in (select user from active)
    and block_timestamp > CURRENT_DATE - 90 group by 1 order by 2 desc), transfer as (select trunc(BLOCK_TIMESTAMP, 'day') as date, count(distinct TX_ID) as Transfer
    from osmosis.core.fact_transfers where TX_STATUS = 'SUCCEEDED' and TRANSFER_TYPE in ('IBC_TRANSFER_OUT', 'OSMOSIS')
    and RECEIVER in (SELECT user from active) and date > CURRENT_DATE - 90 group by 1 order by 1) SELECT n.date, swap, lp, vote, Transfer
    from swap n join lp c on n.date = c.date join vote b on n.date = b.date join transfer m on n.date = m.date order by 1
    Run a query to Download Data