strawbetty2023-03-22 02:51 PM
    with prices as (
      select
      date_trunc('day', hour) as day,
      avg(PRICE) as price
    from ethereum.core.fact_hourly_token_prices
    where symbol='WETH'
    and day >='2022-07-01'
    group by 1
    ), eth_fees as (
    select
      date_trunc('day', x.block_timestamp) as day,
      count(distinct tx_hash) as n_votes,
      avg(TX_FEE) as avg_fee,
      avg(TX_FEE*p.price) as avg_fee_usd,
      sum(TX_FEE) as sum_fees,
      sum(TX_FEE*p.price) as sum_fees_usd
    from ethereum.core.fact_transactions x
    join prices p on x.block_timestamp::date = p.day
    where
      x.block_timestamp >= '2022-07-01'
      and STATUS = 'SUCCESS'
      and x.tx_hash in (select tx_hash from ethereum.aave.ez_votes)
    GROUP BY 1
    ORDER BY 1)
     
    SELECT * FROM eth_fees
    Run a query to Download Data