SELECT

SWAPPER,

BLOCK_TIMESTAMP,

DATEDIFF(second,

LAG(BLOCK_TIMESTAMP) OVER (PARTITION BY SWAPPER ORDER BY BLOCK_TIMESTAMP),

BLOCK_TIMESTAMP) as time_between_swaps

FROM

aptos.defi.ez_dex_swaps

),

trader_activity AS (

SELECT

s.SWAPPER,

COUNT(DISTINCT DATE_TRUNC('day', s.BLOCK_TIMESTAMP)) AS active_days,

COUNT(*) AS total_swaps,

SUM(AMOUNT_IN_USD) AS total_volume,

AVG(AMOUNT_IN_USD) AS avg_swap_size,

AVG(i.time_between_swaps) AS avg_swap_interval,

COUNT(DISTINCT platform) as platforms_used

FROM

aptos.defi.ez_dex_swaps s

LEFT JOIN

swap_intervals i ON s.SWAPPER = i.SWAPPER AND s.BLOCK_TIMESTAMP = i.BLOCK_TIMESTAMP

WHERE

amount_in_usd is not null

GROUP BY

s.SWAPPER

),

trader_categories AS (

SELECT

SWAPPER as "Trader",

CASE

WHEN avg_swap_size > 10000 THEN 'Whale'

WHEN avg_swap_size < 10 and avg_swap_interval > 30 THEN 'Micro-Trader'