select date(RECORDED_AT) as pdate, avg(price) as price

from osmosis.core.dim_prices

where symbol = 'SCRT' and pdate<CURRENT_DATE

group by pdate

),

f as(

select date_trunc('day', block_timestamp) as date,

case when Date>CURRENT_DATE-8 then 'current week' else 'other days' end as "Date status",

count(distinct TX_ID) as swaps,

count(distinct TRADER) as swappers,

sum(case when FROM_CURRENCY = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A' then FROM_AMOUNT/1e6 * price else TO_AMOUNT/1e6 * price end) as usd_vol,

avg(usd_vol) over(order by date asc) as avg_usd_vol,

avg(swaps) over(order by date asc) as avg_swaps_n,

sum(usd_vol) over (order by date asc) as cumu_usd_vol,

sum(swaps) over (order by date asc) as cumu_swaps,

sum(swappers) over (order by date asc) as cumu_swapers,

avg(swappers) over(order by date asc) as avg_swappers_n,

usd_vol/swappers as "daily Volume per user",

price

from osmosis.core.fact_swaps

join ev_price_tbl on ev_price_tbl.pdate = date(block_timestamp)

where (FROM_CURRENCY = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A' OR TO_CURRENCY = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A' )

and date<CURRENT_DATE

group by date, price

)

select *, (select Avg(swaps) from f ) as AVG_Swap

, (select Avg(swappers) from f ) as AVG_Swappers

, (select Avg(usd_vol) from f ) as AVG_Volume

, (select sum(usd_vol) from f ) / (select sum(swappers) from f )as "Volume per user"

from f

order by date asc