WITH attempted_txs AS (

SELECT

DATE_TRUNC('week', block_timestamp) AS week_start,

COUNT(DISTINCT tx_id) AS swap_count,

CASE

-- Trust Wallet detection

WHEN UPPER(SPLIT_PART(MEMO, ':', 5)) IN ('TI', 'TE', 'TR', 'TD') THEN 'Trust Wallet'

WHEN SPLIT_PART(MEMO, ':', 5) ILIKE '-_/%' OR

SPLIT_PART(MEMO, ':', 5) ILIKE '%/-_' OR

SPLIT_PART(MEMO, ':', 5) = '-_' OR -- Exactly "-_"

SPLIT_PART(MEMO, ':', 5) = 't' OR -- Exactly "t"

SPLIT_PART(MEMO, ':', 5) = '-_/t' -- Exactly "-_/t"

THEN 'SwapKit'

ELSE NULL

END AS Affiliate

FROM thorchain.defi.fact_swaps_events

WHERE block_timestamp BETWEEN '2024-01-01' AND '2025-02-20'

GROUP BY week_start, Affiliate

SELECT

weeks.week_start,

-- Trust Wallet data

COALESCE(TW.total_swaps, 0) AS total_swaps_TW,

COALESCE(SUM(TW.total_swaps) OVER (ORDER BY weeks.week_start), 0) AS cum_swaps_TW,

-- SwapKit data (now including '-_', 't', and '-_/t')

COALESCE(SK.total_swaps, 0) AS total_swaps_SK,

COALESCE(SUM(SK.total_swaps) OVER (ORDER BY weeks.week_start), 0) AS cum_swaps_SK

FROM

(SELECT DISTINCT week_start FROM attempted_txs) AS weeks

LEFT JOIN