nitsParticipation
    Updated 2022-06-22
    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