SELECT

origin_from_address AS swapper,

COUNT(DISTINCT tx_hash) AS tx_count,

SUM(amount_usd) AS total_amount_usd

FROM ronin.core.ez_token_transfers

WHERE origin_to_address IN (

'0x7d0556d55ca1a92708681e2e231733ebd922597d', -- old Katana

'0x5f0acdd3ec767514ff1bf7e79949640bf94576bd', --Katana New

'0x77f96cf7b98b963fb8a9b84787806d396d953b2b' -- Affiliator

)

AND amount_usd IS NOT NULL

GROUP BY swapper

SELECT

-- Transaction count distribution

CASE

WHEN tx_count = 1 THEN '1 Tx'

WHEN tx_count BETWEEN 2 AND 5 THEN '2-5 Tx'

WHEN tx_count BETWEEN 6 AND 10 THEN '6-10 Tx'

WHEN tx_count BETWEEN 11 AND 20 THEN '11-20 Tx'

ELSE '21+ Tx'

END AS tx_count_category,

COUNT(DISTINCT swapper) AS user_count

FROM tx_data

GROUP BY tx_count_category

ORDER BY tx_count_category;