with token_price as (
select
date_trunc('day', recorded_at) as day,
avg(price) as price
from osmosis.core.dim_prices
where symbol = 'OSMO'
group by 1
),
block_fee as (
select
date_trunc('hour', block_timestamp) as date,
block_id,
avg(price) * sum((split(fee,'uosmo')[0]::numeric)/1e6) as gas_fee
from osmosis.core.fact_transactions join token_price on block_timestamp::date = day
where date >= '2022-10-01' and date < current_date and
fee ilike '%uosmo%'
group by 1, 2
)
select
date,
avg(gas_fee) as blocks_fee
from block_fee
group by 1