select recorded_at::date as day,

avg (price) as SCRTPrice

from osmosis.core.dim_prices

where symbol = 'SCRT'

and recorded_at >= CURRENT_DATE - 7

group by 1),

maintable as (

select 'Swap From SCRT' as swap_type,

trader,

count (distinct tx_id) as Swaps_Count,

sum (from_amount/1e6) as SCRT_Volume,

sum ((from_amount/1e6)*SCRTprice) as USD_Volume

from osmosis.core.fact_swaps t1 join SCRTpricet t2 on t1.block_timestamp::date = t2.day

where from_currency = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'

and block_timestamp >= CURRENT_DATE - 7

and tx_status = 'SUCCEEDED'

group by 1,2

union ALL

select 'Swap to SCRT' as swap_type,

trader,

count (distinct tx_id) as Swaps_Count,

sum (to_amount/1e6) as SCRT_Volume,

sum ((to_amount/1e6)*SCRTprice) as USD_Volume

from osmosis.core.fact_swaps t1 join SCRTpricet t2 on t1.block_timestamp::date = t2.day

where to_currency = 'ibc/0954E1C28EB7AF5B72D24F3BC2B47BBB2FDF91BDDFD57B74B99E133AED40972A'

and block_timestamp >= CURRENT_DATE - 7

and tx_status = 'SUCCEEDED'

group by 1,2)

select swap_type,

avg (swaps_count), avg (SCRT_volume), avg (usd_volume) from maintable

group by 1