0xHaM-dCosmos
Updated 2022-12-17
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
›
⌄
with txns as (
select
TX_FROM as wallet,
*
from cosmos.core.fact_transactions
where block_timestamp >= '2022-01-01'
),
txns2 as (
select
lag(block_timestamp, 1) ignore nulls over (partition by wallet order by block_timestamp asc) as previous_txn,
datediff('day',previous_txn, block_timestamp) as previous_txn_n_days,
*
from txns
qualify previous_txn is not null
)
select
CASE
WHEN previous_txn_n_days <= 1 THEN 'Daily Users'
WHEN previous_txn_n_days > 1 AND previous_txn_n_days <= 7 THEN 'Weekly Users'
WHEN previous_txn_n_days > 7 AND previous_txn_n_days <= 30 THEN 'Monthly Users'
WHEN previous_txn_n_days > 3 AND previous_txn_n_days <= 90 THEN 'Quarterly Users'
WHEN previous_txn_n_days > 90 AND previous_txn_n_days <= 180 THEN 'Half year Users'
WHEN previous_txn_n_days > 180 THEN 'Yearly Users'
end as distribution,
count(distinct wallet) as n_users_weekly
from txns2
group by 1
Run a query to Download Data