SELECT

recipient AS wallet_addresses,

date_trunc('month', min(block_timestamp)::date)::date AS start_month,

date_trunc('month', max(block_timestamp)::date)::date AS end_month,

datediff(month, start_month, end_month) AS active_months,

LISTAGG(DISTINCT date_trunc('month', block_timestamp)::date, ',') AS active_months_list

FROM ethereum.uniswapv3.ez_swaps

GROUP BY wallet_addresses

),

splitActiveMonths AS (

SELECT

wallet_addresses,

value::date AS active_month

FROM userActiveMonths,

TABLE(SPLIT_TO_TABLE(active_months_list, ',')) -- Split the active_months_list into rows

),

monthlyRetention AS (

SELECT

MONTHNAME(start_month) AS month,

COUNT(start_month) AS new_users,

COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths WHERE splitActiveMonths.wallet_addresses = userActiveMonths.wallet_addresses AND splitActiveMonths.active_month = userActiveMonths.start_month) THEN 1 END) AS M0,

COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths WHERE splitActiveMonths.wallet_addresses = userActiveMonths.wallet_addresses AND splitActiveMonths.active_month = add_months(userActiveMonths.start_month, 1)) THEN 1 END) AS M1,

COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths WHERE splitActiveMonths.wallet_addresses = userActiveMonths.wallet_addresses AND splitActiveMonths.active_month = add_months(userActiveMonths.start_month, 2)) THEN 1 END) AS M2,

COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths WHERE splitActiveMonths.wallet_addresses = userActiveMonths.wallet_addresses AND splitActiveMonths.active_month = add_months(userActiveMonths.start_month, 3)) THEN 1 END) AS M3,

COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths WHERE splitActiveMonths.wallet_addresses = userActiveMonths.wallet_addresses AND splitActiveMonths.active_month = add_months(userActiveMonths.start_month, 4)) THEN 1 END) AS M4,

COUNT(CASE WHEN EXISTS (SELECT 1 FROM splitActiveMonths WHERE splitActiveMonths.wallet_addresses = userActiveMonths.wallet_addresses AND splitActiveMonths.active_month = add_months(userActiveMonths.start_month, 5)) THEN 1 END) AS M5

FROM userActiveMonths

WHERE start_month >= '2023-01-01' AND start_month <= '2023-04-01'

GROUP BY start_month

ORDER BY start_month

),

rotatedRetention AS (