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, d.description, case when b.proposal_id is not null then 'December' else 'November' end as month_str, count(distinct voter) as num_voters, sum(num_voters) over (partition by a.proposal_id) as total_num_voters,

(num_voters/total_num_voters)*100 as total_voters_option_perc 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

left join osmosis.core.dim_vote_options d

on a.vote_option = d.vote_id

where a.proposal_id in (select * from all_proposals)

and tx_succeeded = 'TRUE'

group by 1, 2 , 3