SELECT

decoded_log:toDelegate as delegate,

COUNT(distinct decoded_log:delegator) AS delegators

FROM arbitrum.core.ez_decoded_event_logs

WHERE contract_address = lower('0x912CE59144191C1204E64559FE8253a0e49E6548')

AND event_name = 'DelegateChanged'

--AND decoded_log:toDelegate = '0xababe91a907fadff921720c43abe4098e7245e99'

GROUP BY 1

),

votes AS(

SELECT

block_timestamp,

tx_hash,

decoded_log,

decoded_log:reason :: STRING AS reason,

decoded_log:support AS support,

decoded_log:voter :: STRING AS voter,

decoded_log:weight / POW(10, 18) AS weight

FROM arbitrum.core.ez_decoded_event_logs

WHERE contract_address = '0x789fc99093b09ad01c34dc7251d0c89ce743e5a4'

AND decoded_log:proposalId = '70429238801539452185774644949439873171403711884919863798646613157877144482819'

AND event_name = 'VoteCast'

AND tx_status = 'SUCCESS'

),

votes_new AS(

SELECT

voter,

CASE WHEN support = 1 THEN 'FOR'

WHEN support = 0 THEN 'AGAINST'

ELSE 'ABSTAIN' END AS choice,

weight,

CASE WHEN weight < 500 THEN 'small'

WHEN weight BETWEEN 500 AND 50000 THEN 'medium'

ELSE 'large' END AS category

FROM votes