select proposal_id,

count(DISTINCT tx_id) as votes,

count(DISTINCT voter) as voters,

sum(CASE

WHEN VOTE_OPTION_TEXT = 'Yes' THEN 1

ELSE -1 end) as vote_count

from terra.core.fact_governance_votes

where tx_succeeded='TRUE'

group by 1

),

base as (

select

CASE

WHEN vote_count > 0 THEN 'Passed✅'

ELSE 'Rejected❌' END as status,

count( DISTINCT tx:body:messages[0]:content:title) as Proposal_Title

from terra.core.fact_governance_submit_proposal a

join terra.core.fact_transactions b using(tx_id)

left join voting using (proposal_id)

where proposal_type = 'CommunityPoolSpend'

group by 1

)

select

Proposal_Title ,

status

from base