WITH base_table AS (

-- Data preparation: identify the Swapper and time difference from their first interaction

SELECT

TX_FROM AS Swapper,

--date_trunc('month', block_timestamp) AS month_date,

MIN(date_trunc('month', block_timestamp)) OVER (PARTITION BY Swapper) AS earliest_date,

DATEDIFF(

'month',

MIN(date_trunc('month', block_timestamp)) OVER (PARTITION BY Swapper), -- earliest date

date_trunc('month', block_timestamp) -- current date

) AS difference

FROM solana.core.fact_transfers

WHERE

tx_to = 'ZG98FUCjb8mJ824Gbs6RsgVmr1FhXb2oNiJHa2dwmPd'

AND block_timestamp >= CURRENT_TIMESTAMP() - INTERVAL '1 year'

),

count_new_users AS (

-- Count new users by their first activity month

SELECT

earliest_date,

COUNT(DISTINCT Swapper) AS new_users

FROM base_table

GROUP BY 1

),

count_returning_users AS (

-- Count returning users by month difference

SELECT

earliest_date,

difference,

COUNT(DISTINCT Swapper) AS returning_users

FROM base_table

WHERE difference != 0

GROUP BY 1, 2

),