mz0111near 6
    Updated 2023-05-17
    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