MLDZMNuserapp2
Updated 2023-08-08
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
29
30
31
32
33
34
›
⌄
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