with base as (select 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(to_amount_usd) as swap_volume_usd

from thorchain.defi.fact_swaps

where block_timestamp >= '2024-01-01'

group by tx_id, from_asset, to_asset),

base2 as (select tx_id,

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

sum(swap_volume_usd) as swap_volume

from base

group by 1),

base3 as (select swap_direction[0] as path1,

swap_direction[1] as path2,

when path2 is null then path1

when substr(path1, 1, 4) = 'RUNE' then path2

else path1

end as swap_path1,