select distinct proposal_id as proposal_id from osmosis.core.fact_governance_votes

where tx_succeeded = 'TRUE'

and date_trunc('day', block_timestamp) >= '2022-11-01' and date_trunc('day', block_timestamp) <= '2022-11-30'

),

-- there can be proposals which were active both on november and december, so for december we only keep those active on december for the first time

proposals_december as (

select distinct proposal_id as proposal_id from osmosis.core.fact_governance_votes

where tx_succeeded = 'TRUE'

and date_trunc('day', block_timestamp) >= '2022-12-01' and date_trunc('day', block_timestamp) <= '2022-12-31'

and proposal_id not in (select proposal_id from proposals_november)

),

all_proposals as (

select * from proposals_november

union ALL

select * from proposals_december

select a.proposal_id, case when b.proposal_id is not null then 'December' else 'November' end as month_str, count(distinct voter) as num_voters, rank() over (partition by month_str order by num_voters desc) as rank from osmosis.core.fact_governance_votes a

left join proposals_december b

on a.proposal_id = b.proposal_id

left join proposals_december c

on a.proposal_id = c.proposal_id

where a.proposal_id in (select * from all_proposals)

and tx_succeeded = 'TRUE'

group by 1, 2