SELECT

voter AS delegate,

proposal_title,

CASE WHEN trim(vote_option :: STRING, '[""]') = 1 THEN

'For'

WHEN trim(vote_option :: STRING, '[""]') = 2 THEN

'Against'

ELSE

'Abstain'

END AS vote_option,

tag_name AS value

FROM "ETHEREUM"."CORE"."EZ_SNAPSHOT"

LEFT OUTER JOIN crosschain.core.address_tags

ON LOWER(voter) = LOWER(address)

WHERE space_id = 'opcollective.eth'

AND creator = 'jkhuhnke11'

AND tag_type = 'delegate_value'

AND blockchain = 'optimism'

AND tag_name = '{{value}}'

),

forr AS (

SELECT

delegate,

count(vote_option) as fors

FROM votes

WHERE vote_option = 'For'

group by delegate

),

against AS (

SELECT

delegate,

count(vote_option) as againsts

FROM votes