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,

tx_id,

trader,

from_amount/1e6 as SCRT_Volume,

(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'

union ALL

select 'Swap to SCRT' as swap_type,

tx_id,

trader,

to_amount/1e6 as SCRT_Volume,

(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')

select swap_type,

case when SCRT_Volume < 10 Then 'Less Than 10 SCRT'

when SCRT_Volume >= 10 and SCRT_Volume < 100 then '10 - 100 SCRT'

when SCRT_Volume >= 100 and SCRT_Volume < 1000 then '100 - 1000 SCRT'