SELECT

DISTINCT tx_id,

block_timestamp,

from_address,

from_amount_usd,

affiliate_fee_basis_points,

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,

DATE_TRUNC(week, block_timestamp) AS week,

from_address,

from_amount_usd,

affiliate_address,

affiliate_fee_basis_points,

CASE

WHEN affiliate_address IN ('t', 'T','ts','thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk') THEN 'Thorswap'

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

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

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

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