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)

),

voters AS (

SELECT

count(distinct voter) as "Distinct Voters",

max(polls_voted) as "Max Polls Voted",

count(case when polls_voted > 1 then voter end) as "Voted >1",

count(case when polls_voted > 5 then voter end) as "Voted >5",

count(case when polls_voted > 10 then voter end) as "Voted >10"