Salehs1
Updated 2022-10-19
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with lst_active as (
select
distinct active_users as Active_Users
from (select
date_trunc (week,block_timestamp) as Week
,tx_from as active_users
,count (distinct block_timestamp::date) as days
from osmosis.core.fact_transactions
where tx_status = 'SUCCEEDED'
group by 1,2
having days >= 4)
)
,lst_staking as (
select
'Staking' as type
,date_trunc(week,block_timestamp)::date as date
,count(TX_ID) as tx_count
,count(DISTINCT DELEGATOR_ADDRESS) as wallets
,sum(tx_count) over(order by date) as cum_tx_count
,sum(wallets) over(order by date) as cum_wallets
from osmosis.core.fact_staking
where DELEGATOR_ADDRESS in (select Active_Users from lst_active)
and TX_STATUS='SUCCEEDED'
and ACTION='delegate'
group by 1,2
order by 1
)
,lst_swaps as (
select
'Swaps' as type
,date_trunc(week,block_timestamp)::date as date
,count(TX_ID) as swaps_count
,count(DISTINCT TRADER) as traders
,sum(swaps_count) over(order by date) as cum_swaps_count
,sum(traders) over(order by date) as cum_traders
from osmosis.core.fact_swaps
Run a query to Download Data