SELECT event_inputs:voter AS address

, block_timestamp AS vote_time

FROM ethereum.core.fact_event_logs

WHERE contract_address = '0xd3a9fe267852281a1e6307a1c37cdfd76d39b133'

AND event_inputs:pollId = '832'--in ('814', '815', '832')

),

delegations AS (

SELECT to_address as address, -1*amount as mkr_amount

FROM ethereum.core.ez_token_transfers

JOIN voters ON address = to_address

WHERE contract_address = '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2'

AND from_address = '0x0a3f6849f78076aefadf113f5bed87720274ddc0'

AND block_timestamp <= vote_time

UNION ALL

SELECT from_address as address, amount as mkr_amount

FROM ethereum.core.ez_token_transfers

JOIN voters ON address = from_address

WHERE contract_address = '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2'

AND to_address = '0x0a3f6849f78076aefadf113f5bed87720274ddc0'

AND block_timestamp <= vote_time

),

delegator_balances AS (

SELECT address, SUM(mkr_amount) AS mkr_balance

FROM delegations

GROUP BY address

SELECT v.address

, SUM(CASE

WHEN mkr_balance IS NULL THEN (SELECT AVG(balance)

FROM ethereum.erc20_balances

WHERE user_address = v.address

AND balance_date = vote_time::DATE

AND contract_address = '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2')

ELSE mkr_balance

END) AS mkr_committed