ramishoowUntitled Query
Updated 2022-10-19
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
›
⌄
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