MLDZMNuserapp2
    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
    distinct from_address,
    count(distinct tx_hash) as no_txn,
    min (block_timestamp) as first_transaction,
    datediff(day,first_transaction,CURRENT_DATE) as wallet_age
    from avalanche.core.fact_transactions
    where from_address in (select voter from t1)
    and STATUS = 'SUCCESS'
    group by 1
    )


    select
    case
    when wallet_age<=5 then 'under 5 Days'
    when wallet_age > 5 and wallet_age <= 20 then '5- 20 Days'
    when wallet_age > 20 and wallet_age <= 50 then '20- 50 Days'
    when wallet_age > 50 and no_txn <= 75 then '50- 75 Days'
    when wallet_age > 75 and no_txn <= 100 then '75- 100 Days'
    when wallet_age > 100 then 'over 100 Days'
    end as gp,
    count(distinct from_address) as no_users
    from t2
    group by 1 having gp is not null
    Run a query to Download Data