recorded_at::date as day,

avg (price) as price_token

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by 1),

tb2 as (select

recorded_at::date as day,

avg (price) as price_token

from osmosis.core.dim_prices

where symbol = 'ATOM'

group by 1),

tb3 as (select

date_trunc('day',RECORDED_HOUR) as day,

avg(CLOSE) as price_token

from crosschain.core.fact_hourly_prices

where ID = 'terra-luna-2'

group by 1)

select

date_trunc('week',block_timestamp) as date,

'Osmosis' as chain,

sum(split(fee,'uosmo')[0]::numeric/1e6) as amount,

sum((price_token)*split(fee,'uosmo')[0]::numeric/1e6) as amount_usd,

avg(price_token*split(fee,'uosmo')[0]::numeric/1e6) as avg_usd,

avg(avg_usd) OVER (ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as MA_7_Days,

sum(amount_usd) over (order by date) as cum_volume

from osmosis.core.fact_transactions s left join tb1 b on s.BLOCK_TIMESTAMP::date=b.day

where fee ilike '%uosmo%' and block_timestamp>='2022-01-01'

group by 1,2

union all