SELECT

s.tx_id,

MIN(s.block_timestamp) AS date_time,

AVG(s.from_amount_usd) AS swap_size,

CASE

WHEN AVG(s.from_amount_usd) > 0 AND AVG(s.from_amount_usd) <= 10 THEN 'a. 0 to 10 USD'

WHEN AVG(s.from_amount_usd) > 10 AND AVG(s.from_amount_usd) <= 50 THEN 'b. 10 to 50 USD'

WHEN AVG(s.from_amount_usd) > 50 AND AVG(s.from_amount_usd) <= 100 THEN 'c. 50 to 100 USD'

WHEN AVG(s.from_amount_usd) > 100 AND AVG(s.from_amount_usd) <= 500 THEN 'd. 100 to 500 USD'

WHEN AVG(s.from_amount_usd) > 500 AND AVG(s.from_amount_usd) <= 1000 THEN 'e. 500 to 1000 USD'

WHEN AVG(s.from_amount_usd) > 1000 AND AVG(s.from_amount_usd) <= 5000 THEN 'f. 1000 to 5000 USD'

WHEN AVG(s.from_amount_usd) > 5000 AND AVG(s.from_amount_usd) <= 10000 THEN 'g. 5000 to 10000 USD'

WHEN AVG(s.from_amount_usd) > 10000 THEN 'h. More than 10000 USD'

ELSE 'null'

END AS category

FROM maya.defi.fact_swaps AS s

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

GROUP BY s.tx_id

),

filtered_swaps AS (

SELECT

tx_id,

CASE

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

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

SPLIT_PART(MEMO, ':', 5) ILIKE '%ts%' OR

SPLIT_PART(MEMO, ':', 5) = '_' OR

SPLIT_PART(MEMO, ':', 5) = 'ts' OR

SPLIT_PART(MEMO, ':', 5) = '_/ts'

THEN 'SwapKit'

ELSE NULL

END AS Affiliate_SK,

CASE

WHEN SPLIT_PART(MEMO, ':', 4) NOT LIKE '%/%' OR SPLIT_PART(MEMO, ':', 4) LIKE '%/1/0' THEN 'Regular Swap'

ELSE 'Streaming Swap'