with pools_count as (

select

tx_id,

count(DISTINCT pool_name) as n_pools

from thorchain.defi.fact_swaps

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

group by 1

)

select

CASE

WHEN affiliate_address IN ('t','t/-','tl','T','ts','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk','thor17suv0n437snv68axkx64whutkrvefv7pzq7xep') THEN 'Thorswap'

WHEN affiliate_address IN ('ti', 'te','tr', 'td','tb') THEN 'Trust Wallet'

WHEN affiliate_address IN ('dx','thor1ap5vn4svwkpch2c9jm7hlpr2pj47e62xwpcvtw') THEN 'Asgardex'

WHEN affiliate_address IN ('wr','thor1a427q3v96psuj4fnughdw8glt5r7j38lj7rkp8') THEN 'Thorwallet'

WHEN affiliate_address IN ('va', 'vi','v0') THEN 'Vultisig'

WHEN affiliate_address IN ('zcx-com','0xbami','unizen-utxo') THEN 'Unizen'

WHEN affiliate_address = 'ds' THEN 'Defispot'

WHEN affiliate_address IN ('rg','ro') THEN 'Rango'

WHEN affiliate_address = 'ss' THEN 'Shapeshift'

WHEN affiliate_address = 'lends' THEN 'Lends'

WHEN affiliate_address = 'bp' THEN 'BitPay'

WHEN affiliate_address = 'dcf' THEN 'Decentralfi'

WHEN affiliate_address = 'tl' THEN 'Thorswap Ledger'

WHEN affiliate_address = 'ej' THEN 'Edge Wallet'

WHEN affiliate_address IN ('lifi','lifi/-_') THEN 'Lifi'

WHEN affiliate_address IN ('okw','okw/-_') THEN 'OKX Wallet'

WHEN affiliate_address = 'sy' THEN 'Symbiosis'

WHEN affiliate_address IN('oky','OKY') THEN 'OneKey'

WHEN affiliate_address IN ('xdf','XDF') THEN 'XDEFI'

WHEN affiliate_address = 'zengo' THEN 'Zengo'

WHEN affiliate_address = 'cb' THEN 'Coinbot'

WHEN affiliate_address = 'pl' THEN 'Plexus'

WHEN affiliate_address = 'bqx' THEN 'Ethos'

WHEN affiliate_address = 'kf' THEN 'Babylon (Kensho)'

WHEN affiliate_address = 'ferz' THEN 'Ferz Wallet'