SELECT

platform,

swapper,

MIN(block_timestamp) AS first_swap

FROM

aptos.defi.ez_dex_swaps

WHERE

block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'

AND (token_in = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT'

OR token_out = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT')

GROUP BY

platform, swapper

),

repeat_swappers AS (

SELECT

f.platform,

COUNT(DISTINCT s.swapper) AS repeat_swappers_count

FROM

first_time_swappers f

JOIN

aptos.defi.ez_dex_swaps s ON f.swapper = s.swapper AND f.platform = s.platform

WHERE

s.block_timestamp > f.first_swap

AND s.block_timestamp BETWEEN '2024-01-01' AND '2024-12-31'

AND (s.token_in = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT'

OR s.token_out = '0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asset::USDT')

GROUP BY

f.platform

f.platform,

COUNT(DISTINCT f.swapper) AS total_first_time_swappers,

COALESCE(r.repeat_swappers_count, 0) AS repeat_swappers_count,

ROUND((COALESCE(r.repeat_swappers_count, 0) / NULLIF(COUNT(DISTINCT f.swapper), 0)) * 100, 2) AS retention_rate