SELECT

block_timestamp,

tx_hash,

origin_from_address AS swapper,

symbol_in,

symbol_out,

platform,

COALESCE(amount_in_usd, amount_out_usd) AS swap_amount_usd

FROM avalanche.defi.ez_dex_swaps

WHERE origin_to_address = '0x1dac23e41fc8ce857e86fd8c1ae5b6121c67d96d'

AND block_timestamp >= '2024-01-01'

AND swap_amount_usd is not null

),

swapper_count AS (

SELECT

swapper,

count(distinct tx_hash) AS tx_count,

SUM(swap_amount_usd) AS total_volume

FROM okx_aggregator_swaps

GROUP BY swapper

SELECT

CASE

WHEN total_volume < 100 THEN '< $100'

WHEN total_volume BETWEEN 100 AND 500 THEN '$100 - $500'

WHEN total_volume BETWEEN 500 AND 1000 THEN '$500 - $1K'

WHEN total_volume BETWEEN 1000 AND 5000 THEN '$1K - $5K'

ELSE '> $5K'

END AS volume_category,

COUNT(swapper) AS num_swappers

FROM swapper_count

GROUP BY volume_category

ORDER BY volume_category;