from avalanche.core.dim_labels

),

t2 as (select

distinct voter

from ethereum.core.ez_snapshot

where NETWORK = 'Avalanche C-Chain'

and SPACE_ID = 'hubbleexchange.eth'

and PROPOSAL_TITLE in ('HP028 - Validator Mining Program',

'HP026 - Onboard an Oracle Provider, Block Explorer and Node Manager',

),

t3 as (select

distinct from_address,

count (Distinct block_timestamp::Date) as active_days,

min(block_timestamp::date) as first_interaction,

DATEDIFF(day,first_interaction,current_date) as available_days,

active_days/available_days*100 as share_active_days

from avalanche.core.fact_transactions

where from_address in (select voter from t2)

and STATUS = 'SUCCESS'

group by 1

select

case

when share_active_days < 5 then 'Less than 5 percent of days'

when share_active_days >= 5 and share_active_days < 25 then '5-25 percent of days'

when share_active_days >= 25 then 'Over 25 percent of days'

end as user_type,

count(distinct from_address) as no_users

from t3