SELECT

DATE_TRUNC('day', block_timestamp) AS swap_date,

affiliate_address,

SUM(from_amount_usd / rune_usd) AS swap_volume_rune,

COALESCE(affiliate_fee_basis_points, 0) AS affiliate_fee_bp

FROM thorchain.defi.fact_swaps

WHERE block_timestamp::date >= '2024-07-01'

AND block_timestamp::date <= '2024-10-31'

GROUP BY swap_date, affiliate_address, affiliate_fee_basis_points

),

swap_fees AS (

SELECT

swap_date,

affiliate_address,

SUM(swap_volume_rune) AS swap_volume_rune,

affiliate_fee_bp,

SUM(swap_volume_rune * affiliate_fee_bp / 10000) AS affiliate_fee_collected

FROM raw_swap_data

GROUP BY swap_date, affiliate_address, affiliate_fee_bp

),

affiliates_mapped AS (

SELECT

swap_date AS date,

CASE

WHEN affiliate_address IN ('t', 'T', 'thor160yye65pf9rzwrgqmtgav69n6zlsyfpgm9a7xk') THEN 'THORSwap'

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

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

WHEN affiliate_address = 'tl' THEN 'TS Ledger'

WHEN affiliate_address = 'cb' THEN 'Team CoinBot'

WHEN affiliate_address = 'dx' THEN 'Asgardex'

WHEN affiliate_address = 'ss' THEN 'ShapeShift'

WHEN affiliate_address = 'xdf' THEN 'xDEFI'

WHEN affiliate_address = 'rg' THEN 'Rango'