with table_1 as (select min(block_timestamp) as day,

from_address,

native_to_address,

affiliate_fee_basis_points,

tx_id,

split(from_asset, '-')[0] as from_assets,

case

when from_assets ilike '%/%' then split(from_assets, '/')[1]

else split(from_assets, '.')[1]

end as from_asset_names,

split(to_asset, '-')[0] as to_assets,

case

when to_assets ilike '%/%' then split(to_assets, '/')[1]

else split(to_assets, '.')[1]

end as to_asset_names,

concat(from_asset_names, ' -> ', to_asset_names) as assets,

case when assets ilike '%RUNE' then 2

else 1

end as numbering,

sum(from_amount_usd/rune_usd) as rune_volume,

sum(from_amount_usd) as usd_volume

from thorchain.defi.fact_swaps

where affiliate_address = 'ss'

group by 2,3,4,5,6,7,8),

table_2 as (select day,

tx_id,

from_address,

native_to_address,

affiliate_fee_basis_points,

array_agg(distinct assets) within group (order by assets asc) as swap_direction,

sum(rune_volume) as swap_volume_rune,

sum(usd_volume) as swap_volume_usd

from table_1