WITH attempted_txs AS (

SELECT

DISTINCT tx_id,

block_timestamp,

from_address,

from_amount_usd,

affiliate_address

FROM

thorchain.defi.fact_swaps

WHERE

block_timestamp >= '2024-01-01'

),

successful_txs AS (

SELECT *

FROM attempted_txs AS a

WHERE NOT EXISTS (

SELECT 1

FROM thorchain.defi.fact_refund_events AS r

WHERE r.tx_id = a.tx_id

)

),

affiliate_swaps AS (

SELECT

DISTINCT tx_id,

from_address,

from_amount_usd,

affiliate_address,

CASE

WHEN affiliate_address IN ('t', 'T','ts','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk','thor17suv0n437snv68axkx64whutkrvefv7pzq7xep','tl') 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') THEN 'Unizen'