SalehDaily Active User-compare
Updated 2022-10-17
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 as (
select
tx_from
,date_trunc(week, BLOCK_TIMESTAMP)::date as date
,dayname(BLOCK_TIMESTAMP) as day_name
,count(DISTINCT day_name) as tx
from osmosis.core.fact_transactions
where TX_STATUS='SUCCEEDED'
-- where tx_from='osmo1ue2dx7385tc96kljvkhsdgua9ch5zqldka90t5'
group by 1,2,3
)
,lst_week as (
select
tx_from
,day_name
,count(tx) as weeks_active_count
--------------------------------------------------------activity_days-------------------------------------------
,datediff(day,(select min(BLOCK_TIMESTAMP) from osmosis.core.fact_transactions f where f.tx_from=t.tx_from)
,(select max(BLOCK_TIMESTAMP) from osmosis.core.fact_transactions f where f.tx_from=t.tx_from)) as activity_days
--------------------------------------------------------end activity_days---------------------------------------
,floor(ACTIVITY_DAYS/7) as weeks_count
from lst t
group by 1,2
having weeks_count=weeks_active_count
)
,lst_acitve as (
select
tx_from
,count(DAY_NAME) as "number active days in all weeks"
from lst_week
group by 1
having "number active days in all weeks">=4
)
,lst_staking as (
select
Run a query to Download Data