b.ADDRESS_NAME as DAO,

PROPOSAL,

VOTE_CHOICE,

min(BLOCK_TIMESTAMP) as start_time,

count (distinct VOTER) as y_voters ,

sum(VOTE_WEIGHT)as y_weight

from solana.core.fact_proposal_votes a left join solana.core.dim_labels b on a.PROGRAM_NAME=b.ADDRESS

where REALMS_ID =''

and VOTE_CHOICE = 'YES'

group by 1,2,3,4),

no as (select GOVERNANCE_PLATFORM,

b.ADDRESS_NAME as DAO,

PROPOSAL,

VOTE_CHOICE,

min(BLOCK_TIMESTAMP) as start_time,

count (distinct VOTER) as n_voters ,

sum(VOTE_WEIGHT)as n_weight

from solana.core.fact_proposal_votes a left join solana.core.dim_labels b on a.PROGRAM_NAME=b.ADDRESS

where REALMS_ID ='3MMDxjv1SzEFQDKryT7csAvaydYtrgMAc3L9xL9CVLCg'

and VOTE_CHOICE = 'NO'

group by 1,2,3,4)

select case when y.GOVERNANCE_PLATFORM is null then n.GOVERNANCE_PLATFORM else y.GOVERNANCE_PLATFORM end as GOVERNANCE_PLATFORM,

case when y.DAO is null then n.DAO else y.DAO end as DAO,

case when y.PROPOSAL is null then n.PROPOSAL else y.PROPOSAL end as PROPOSAL,

case when y.start_time is null then n.start_time else y.start_time end as Start_time,

case when y_voters is null then 0 else y_voters end as Yes_voters,

case when y_weight is null then 0 else y_weight end as Yes_weight,

case when n_voters is null then 0 else n_voters end as No_voters,

case when n_weight is null then 0 else n_weight end as No_weight,

case when yes_weight> no_weight then 'YES' else 'NO' end as Result,

case when no_voters ='0' then 100 else (yes_voters/no_voters) end as "VOTERS RATIO (Yes/No)",

case when no_weight ='0' then 100 else (yes_weight/no_weight) end as "WEIGHT RATIO (Yes/No)"

from yes y left join no n on y.PROPOSAL=n.PROPOSAL

group by 1,2,3,4,5,6,7,8