SELECT

DATE_TRUNC('week', block_timestamp) AS week_start,

COUNT(DISTINCT tx_id) AS refund_count,

CASE

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

ELSE NULL

END AS Affiliate

FROM thorchain.defi.fact_refund_events

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

GROUP BY week_start, Affiliate

SELECT

week_start,

SUM(refund_count) AS total_refund_count,

SUM(SUM(refund_count)) OVER (ORDER BY week_start) AS cum_refund

FROM refund_data

WHERE Affiliate = 'Trust Wallet'

GROUP BY week_start

ORDER BY week_start DESC;