select

date_trunc('day', recorded_at) as date,

avg(price) as osmo_usd

from osmosis.core.dim_prices

where

symbol = 'OSMO'

group by 1

),

info as (

select

date_trunc('week', block_timestamp) as "Day",

sum((substring (fee,0,charindex('uosmo',fee)-1)/pow(10,6)) * osmo_usd) as "fee",

avg((substring (fee,0,charindex('uosmo',fee)-1)/pow(10,6)) * osmo_usd) as "Avg tx fee",

avg("fee") over (order by "Day", "Day" rows between 6 preceding and current row) as "MA7 fee",

sum("fee") over (order by "Day") as "Cum fee"

from osmosis.core.fact_transactions join price on block_timestamp::date = date

where fee like '%uosmo%'

group by 1

),

avg_info as (

select

avg("fee") as "AVG fee",

avg("Avg tx fee") as "AVG tx fee per day"

from info

select *

from info, avg_info

order by "Day"