SELECT

DATE_TRUNC('month', block_timestamp) AS month,

swapper AS "Swapper"

FROM

solana.defi.ez_dex_swaps

WHERE

block_timestamp BETWEEN DATE_TRUNC('month', DATEADD('month', -2, CURRENT_DATE)) AND CURRENT_DATE

GROUP BY

month,

"Swapper"

),

EthereumSwappers AS (

SELECT

DATE_TRUNC('month', block_timestamp) AS month,

origin_from_address AS "Swapper"

FROM

ethereum.defi.ez_dex_swaps

WHERE

block_timestamp BETWEEN DATE_TRUNC('month', DATEADD('month', -2, CURRENT_DATE)) AND CURRENT_DATE

GROUP BY

month,

"Swapper"

),

SolanaRetention AS (

SELECT

a.month AS initial_month,

COUNT(DISTINCT a."Swapper") AS initial_swappers,

COUNT(DISTINCT CASE WHEN b."Swapper" IS NOT NULL THEN a."Swapper" END) AS retained_swappers

FROM

SolanaSwappers a

LEFT JOIN SolanaSwappers b ON a."Swapper" = b."Swapper" AND b.month = DATEADD('month', 1, a.month)

WHERE

a.month = DATE_TRUNC('month', DATEADD('month', -1, CURRENT_DATE))

GROUP BY

a.month