MLDZMNvuser1
    Updated 2023-08-08
    with t1 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',
    'HP027 - Onboarding Designated Market Maker')
    ),

    t2 as (select
    count(distinct PROPOSAL_TITLE) as total_proposals
    from ethereum.core.ez_snapshot
    where NETWORK = 'Avalanche C-Chain'
    and SPACE_ID = 'hubbleexchange.eth'
    ),

    t3 as (select
    distinct voter,
    count(distinct PROPOSAL_TITLE) as no_proposals,
    total_proposals,
    no_proposals/total_proposals*100 as share_participation
    from ethereum.core.ez_snapshot , t2
    where NETWORK = 'Avalanche C-Chain'
    and SPACE_ID = 'hubbleexchange.eth'
    and voter in (select voter from t1)
    group by 1,total_proposals
    )

    select
    case
    when share_participation<25 then 'Less than 25% of proposals'
    when share_participation>=25 and share_participation<50 then '25-50% of proposals'
    when share_participation>=50 and share_participation<75 then '50-75% of proposals'
    when share_participation>=75 then 'More than 75% of proposals'
    end as bucket,