kiacryptoNumber of active users in terms of activity
Updated 2022-10-20Copy Reference Fork
99
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 DAUs as (
select
date_trunc('week', block_timestamp) as day,
tx_from as addr,
count(distinct block_timestamp::date) as tx_count
from osmosis.core.fact_transactions
where tx_status = 'SUCCEEDED'
group by 1, 2 having tx_count >= 4
),
-- all_transactions as (
-- select
-- date_trunc('week', block_timestamp) as date,
-- count(distinct tx_from) as active_user,
-- count(distinct tx_id) as txs_count,
-- avg(active_user) over (order by date, date rows between 6 preceding and current row) as ma7_active_user,
-- avg(txs_count) over (order by date, date rows between 6 preceding and current row) as ma7_txs_count,
-- sum(txs_count) over (order by date) as cum_txs_count
-- from osmosis.core.fact_transactions join DAUs on block_timestamp::date = day and addr = tx_from
-- group by 1
-- ),
governance as (
select depositor as wallet, tx_id, block_timestamp
from osmosis.core.fact_governance_proposal_deposits join DAUs on block_timestamp::date = day and addr = depositor
where tx_status = 'SUCCEEDED'
union all
select voter as wallet, tx_id, block_timestamp
from osmosis.core.fact_governance_votes join DAUs on block_timestamp::date = day and addr = voter
where tx_status = 'SUCCEEDED'
),
gov as (
select
count(distinct wallet) as active_user,
count(distinct tx_id) as txs_count,
Run a query to Download Data