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)

),

FINAL AS (

SELECT

pollid as "Poll ID",

1 as count,

sum(tx_fee) as "Total Fees (ETH)",

avg(tx_fee) as "Avg Total Fees (ETH)",

sum(gas_usd_at_tx) as "Total Fees (USD)",

avg(gas_usd_at_tx) as "Avg Tx Fees (USD)",