SELECT

voter,

proposal_id,

block_timestamp

FROM cosmos.gov.fact_governance_votes

qualify row_number() over (partition by voter ORDER BY block_timestamp) = 1

,

voters as (

SELECT

proposal_id,

count(DISTINCT voter) as voter

FROM cosmos.gov.fact_governance_votes

GROUP BY 1

),

new_voters as (

SELECT

proposal_id,

count(DISTINCT voter) as new_voter

FROM new

GROUP BY 1

SELECT

a.proposal_id,

voter,

new_voter,

CASE when a.proposal_id = 848 then 1 else 0 end as type

FROM voters a

LEFT JOIN new_voters b using(proposal_id)

ORDER BY new_voter DESC