select block_timestamp::date as day,

tx_id,

affiliate_address,

affiliate_fee_basis_points,

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

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

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

else split (Token_IN,'.')[1] end as Symbol_IN,

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

else split (Token_OUT, '.')[1] end as Symbol_OUT,

Symbol_IN || ' -> ' || Symbol_OUT as Swap_Pair,

sum (from_amount_usd) as USDVolume1,

sum (from_amount_usd/rune_usd) as RUNEVolume1

from thorchain.defi.fact_swaps

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

AffiliateT as (

select day,

tx_id,

affiliate_address,

affiliate_fee_basis_points,

sum (RUNEVolume1) as RUNEVolume2,

sum (USDVolume1) as USDVolume2,

array_agg (distinct Swap_Pair) within group (order by Swap_Pair) as DIR,

from Assetst

group by 1,2,3,4),

Mintable as (

select tx_id,

affiliate_address,

affiliate_fee_basis_points,

DIR [0] as Path0,

DIR [1] as Path1,

case when Path1 is null then Path0

when substr (Path0,1,4) = 'RUNE' then Path1