WITH prices AS (

SELECT

date_trunc('day', hour) as price_date,

avg(close) as pr

FROM crosschain.price.fact_prices_ohlc_hourly

WHERE hour >= '2024-12-04'

and hour < '2025-01-15'

AND asset_id = 'ethereum'

AND provider = 'coingecko'

GROUP BY 1),

tx AS (

select

BLOCK_TIMESTAMP::date as date,

sum(fee/pow(10,9)) AS feth,

sum(fee/pow(10,9))*avg(pr) as fusd,

count(distinct tx_id) as txs

from eclipse.core.fact_transactions

join prices

on BLOCK_TIMESTAMP::date = price_date

WHERE BLOCK_TIMESTAMP >= '2024-12-04'

and BLOCK_TIMESTAMP < '2025-01-15'

AND INSTRUCTIONS[1]:programId = 'turboe9kMc3mSR8BosPkVzoHUfn5RVNzZhkrT2hdGxN'

AND SUCCEEDED = 'TRUE'

group by all)

sum(feth) AS fee_eth,

sum(fusd) AS fee_usd,

sum(fusd)/sum(txs) AS avg_fee

FROM tx