with polls AS (

SELECT

voter,

block_timestamp,

VOTE_OPTION,

tx_hash,

proposal_id as pollid

FROM ethereum.maker.ez_governance_votes

WHERE tx_status ='SUCCESS'

AND tx_hash !='0xf609ea50ec79eee06d58f1a4a215ab78c91f57da9a614606e1a2267a84f0b0be' -- some weird poll

),

current_price_Eth AS (

SELECT

avg(price) as current_eth_price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol ='WETH' AND date(hour) = date(getdate())

),

gas AS ( -- find gas price

SELECT

tx_fee,

tx_hash as tx_gas,

tx_fee*price as gas_usd_at_tx,

tx_fee*current_eth_price as gas_usd_today

FROM ethereum.core.fact_transactions

left join current_price_Eth

left join (SELECT hour, price FROM ethereum.core.fact_hourly_token_prices WHERE symbol ='WETH') ON date_trunc('hour', block_timestamp) = hour

WHERE tx_hash IN (SELECT distinct tx_hash FROM polls)

),

maker_locked AS (

SELECT

date_trunc('month', block_timestamp) as month,

sum(bal_delta) as "Balance Change",

sum("Balance Change") over (order by month) as "Maker Locked"

FROM ethereum.core.ez_balance_deltas

WHERE contract_address = '0xa618e54de493ec29432ebd2ca7f14efbf6ac17f7' --erc20 minted as IOU