select hour::date as day,

avg (price) as ETHPrice

from ethereum.core.fact_hourly_token_prices

where hour >= CURRENT_DATE - 180

and symbol = 'WETH'

group by 1

),

maintable as (

select voter,

count (Distinct tx_hash) as Votes_Count,

count (distinct voter) as Voters_Count,

count (Distinct proposal_id) as Proposals_Count,

sum (tx_fee) as Gas_Fee,

avg (tx_fee) as Average_Gas_Fee,

median (tx_fee) as Median_Gas_Fee,

max (tx_fee) as Maximum_Gas_fee,

min (tx_fee) as Minimum_Gas_Fee,

sum (tx_fee*ethprice) as USD_Gas_Fee,

avg (tx_fee*ethprice) as USD_Average_Gas_Fee,

median (tx_fee*ethprice) as USD_Median_Gas_Fee,

max (tx_fee*ethprice) as USD_Maximum_Gas_fee,

min (tx_fee*ethprice) as USD_Minimum_Gas_Fee

from ethereum.aave.ez_votes t1 join ethereum.core.fact_transactions t2 using (tx_hash)

join ethprice t3 on t1.block_timestamp::date = t3.day

where block_timestamp >= CURRENT_DATE - 180

group by 1)

select case when Gas_Fee < 0.001 then 'Less Than 0.0001 ETH'

when Gas_Fee >= 0.001 and Gas_Fee < 0.002 then '0.001 - 0.002 ETH'

when Gas_Fee >= 0.002 and Gas_Fee < 0.003 then '0.002 - 0.003 ETH'

when Gas_Fee >= 0.003 and Gas_Fee < 0.004 then '0.003 - 0.004 ETH'

when Gas_Fee >= 0.004 and Gas_Fee < 0.005 then '0.004 - 0.005 ETH'

else 'More Than 0.005 ETH' end as type,

count (Distinct voter) as Voters_Count