mz0111near 6
Updated 2023-05-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
28
›
⌄
with tab1 as
(select
TX_SIGNER as User,
count(distinct block_timestamp::date) as "Number of Active Days",
b.LABEL_TYPE
from near.core.fact_transactions a
join near.core.dim_address_labels b
on b.ADDRESS = a.TX_RECEIVER
where TX_STATUS = 'Success'
and BLOCK_TIMESTAMP :: date >= current_date - {{period}}
group by 1 , 3
)
SELECT
LABEL_TYPE,
case
when "Number of Active Days" = 1 then 'only 1 day activity'
when "Number of Active Days" > 1 and "Number of Active Days" <= 10 then '1-10 days activity'
when "Number of Active Days" > 10 and "Number of Active Days" <= 20 then '10-20 days activity'
when "Number of Active Days" > 20 and "Number of Active Days" <= 30 then '20-30 days activity'
when "Number of Active Days" > 30 and "Number of Active Days" <= 40 then '30-40 days activity'
when "Number of Active Days" > 40 and "Number of Active Days" <= 50 then '40-50 days activity'
when "Number of Active Days" > 50 then 'more than 50 day activity'
end as "activity time",
count(*) as active_users
from tab1
group by 1 , 2
Run a query to Download Data