SELECT hour::date AS price_day, AVG(price) AS price

FROM ethereum.price.ez_prices_hourly

WHERE symbol = 'WETH'

AND hour::date >= '2024-12-06'

GROUP BY 1

),

total_transactions as(

SELECT

*, tx_fee * price AS tx_fee_usd

FROM ink.core.fact_transactions

left join prices on (block_timestamp::date = price_day)

WHERE block_timestamp::date >= '2024-12-09 '

SELECT

block_timestamp::date AS day,

count(DISTINCT tx_hash) as transactions,

count(DISTINCT from_address) AS user,

SUM(tx_fee) AS fees,

AVG(tx_fee) AS average_fee,

SUM(tx_fee_usd) AS fee_usd,

avg(tx_fee_usd) AS average_fee_usd

FROM total_transactions

WHERE block_timestamp::date >= '2024-12-09 '

group by 1