SELECT

DATE_TRUNC('day', block_timestamp) AS swap_date,

origin_from_address

FROM ronin.core.ez_decoded_event_logs

WHERE event_name = 'Swap'

AND block_timestamp >= CURRENT_DATE - INTERVAL '7 days'

),

cohort_base AS (

SELECT

swap_date AS cohort_date,

origin_from_address

FROM daily_swappers

),

retention_tracking AS (

SELECT

b.cohort_date,

d.swap_date,

COUNT(DISTINCT d.origin_from_address) AS retained_users

FROM cohort_base b

JOIN daily_swappers d

ON b.origin_from_address = d.origin_from_address

AND d.swap_date > b.cohort_date

GROUP BY b.cohort_date, d.swap_date

),

cohort_counts AS (

SELECT

cohort_date,

COUNT(DISTINCT origin_from_address) AS initial_users

FROM cohort_base

GROUP BY cohort_date