nitsParticipation
Updated 2022-06-22
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
›
⌄
with prop_times as
(SELECT *, TIMESTAMPADD(SQL_TSI_DAY, 14, day) as finish_date from
(SELECT date(block_timestamp) as day, min(proposal_id) as proposals
from osmosis.core.fact_governance_submit_proposal
GROUP by 1 )
where finish_date <= CURRENT_DATE - 1),
propos as
(SELECT voter, f_U,l_u, total_proposals, min(proposals) as first_proposal, 252-first_proposal as total_available
from
( SELECT voter, min(block_timestamp) as f_u, count(DISTINCT proposal_id) as total_proposals, max(block_timestamp) as l_u from osmosis.core.fact_governance_votes
GROUP by 1 )
inner join prop_times
on f_u < finish_date
GROUP by 1,2 , 3 , 4 )
SELECT distribution, count(DISTINCT voter) as total_users from
(SELECT *, case when total_proposals >= total_available *0.9 then '90-100%'
when total_proposals < total_available *0.9 and total_proposals >= total_available *0.75 then '75-90%'
when total_proposals < total_available *0.75 and total_proposals >= total_available *0.5 then '50-75%'
when total_proposals < total_available *0.5 and total_proposals >= total_available *0.25 then '25-50%'
when total_proposals < total_available *0.25 and total_proposals >= total_available *0.1 then '10-25%'
when total_proposals < total_available *0.1 then '<10%' end as distribution
from propos)
GROUP by 1
limit 100
Run a query to Download Data