with price as ( select recorded_at::date as day, avg (price) as Eprice

from osmosis.core.dim_prices

where symbol = 'EVMOS'

and recorded_at >= CURRENT_DATE - 7

group by 1)

select 'Swap From EVMOS' as swap_type, block_timestamp::date as date, count (distinct tx_id) as Swaps_Count, count (distinct trader) as Swappers_Count,

sum (from_amount/1e18) as EVMOS_Volume, sum ((from_amount/1e18)*EPrice) as USD_Volume, avg (from_amount/1e18) as AVG_EVMOS_Volume,

avg ((from_amount/1e18)*EPrice) as AVG_USD_Volume

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

where from_currency = 'ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A'

and block_timestamp >= CURRENT_DATE - 7

and tx_status = 'SUCCEEDED'

group by 1, 2

union ALL

select 'Swap to EVMOS' as swap_type, block_timestamp::date as date, -count (distinct tx_id) as Swaps_Count, -count (distinct trader) as Swappers_Count,

-sum (to_amount/1e18) as EVMOS_Volume, -sum ((to_amount/1e18)*EPrice) as USD_Volume, -avg (to_amount/1e18) as AVG_EVMOS_Volume,

-avg ((to_amount/1e18)*EPrice) as AVG_USD_Volume

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

where to_currency = 'ibc/6AE98883D4D5D5FF9E50D7130F1305DA2FFA0C652D1DD9C123657C6B4EB2DF8A'

and block_timestamp >= CURRENT_DATE - 7

and tx_status = 'SUCCEEDED'

group by 1, 2