alirsOsmos-G-05
    Updated 2022-11-23
    WITH active_users
    AS (
    SELECT DISTINCT voter AS active_user
    ,min(block_timestamp) AS min_time
    FROM osmosis.core.fact_governance_votes
    GROUP BY 1
    )
    ,proposals
    AS (
    SELECT DISTINCT proposal_id
    ,min(block_timestamp) AS proposal_min_time
    ,count(DISTINCT tx_id) AS votes
    ,count(DISTINCT voter) AS participants
    FROM osmosis.core.fact_governance_votes
    GROUP BY 1
    )
    SELECT proposal_id
    ,participants
    ,votes
    ,count(DISTINCT active_user) AS active_users
    FROM proposals
    ,active_users
    WHERE min_time::DATE < proposal_min_time::DATE
    AND proposal_id in(354,355,356,357,358,359,360,361,362,363,364)
    GROUP BY 1
    ,2
    ,
    order by 1