select

block_timestamp,

tx_id,

from_amount_usd,

to_amount_usd,

from_address,

array_size(affiliate_addresses_array) as aff_adds_count,

when aff_adds_count > 1 and affiliate_address = '_' then affiliate_addresses_array[1]

else affiliate_address

end as affiliate_address,

case

when aff_adds_count > 1 and affiliate_address = '_' then AFFILIATE_FEE_BASIS_POINTS_ARRAY[1]

else AFFILIATE_FEE_BASIS_POINTS end as AFFILIATE_FEE_BASIS_POINTS

from maya.defi.fact_swaps

where tx_id not in (select tx_id from maya.defi.fact_refund_events)

),

affiliate as (

select

case

when affiliate_address = 'ts' then 'ThorSwap'

when affiliate_address = 'wr' then 'THORWallet'

when affiliate_address = 'dx' then 'Asgardex'

when affiliate_address IN ('ELD','eld') then 'DoritoDEX'

when affiliate_address IN ('cs', 'CS') then 'CacaoSwap'

when affiliate_address = 'rg' then 'Rango'

when affiliate_address = 'ej' then 'Edge Wallet'

when affiliate_address IN ('vi', 'va', 'v0') then 'Vultisig'

when affiliate_address = 'leo' then 'LeoDex'

when affiliate_address = 'dp' then 'Astrolescent'

when affiliate_address = 'ms' then 'ClioSwap'

when affiliate_address = 'ds' then 'Defispot'

when affiliate_address = 'moca' then 'Moca'

when affiliate_address = 'is' then 'Instaswap'