SELECT DISTINCT SWAPPER

FROM aptos.defi.ez_dex_swaps

WHERE BLOCK_TIMESTAMP >= DATEADD(day, -{{days}}, CURRENT_DATE)

),

past_swappers AS (

SELECT DISTINCT SWAPPER

FROM aptos.defi.ez_dex_swaps

WHERE BLOCK_TIMESTAMP < DATEADD(day, -{{days}}, CURRENT_DATE)

PLATFORM,

COUNT(DISTINCT CASE WHEN r.SWAPPER IS NOT NULL AND p.SWAPPER IS NOT NULL THEN r.SWAPPER END) AS repeat_swappers,

COUNT(DISTINCT r.SWAPPER)-COUNT(DISTINCT CASE WHEN r.SWAPPER IS NOT NULL AND p.SWAPPER IS NOT NULL THEN r.SWAPPER END) AS non_repeat_swappers,

(COUNT(DISTINCT CASE WHEN r.SWAPPER IS NOT NULL AND p.SWAPPER IS NOT NULL THEN r.SWAPPER END) * 1.0 /

COUNT(DISTINCT r.SWAPPER)) * 100 AS repeat_rate

aptos.defi.ez_dex_swaps s

LEFT JOIN recent_swappers r ON s.SWAPPER = r.SWAPPER

LEFT JOIN past_swappers p ON s.SWAPPER = p.SWAPPER

BLOCK_TIMESTAMP >= DATEADD(day, -{{days}}, CURRENT_DATE)

GROUP BY

PLATFORM

ORDER BY

repeat_rate DESC