SELECT

from_address,

MIN(DATE_TRUNC('month', block_timestamp)) AS first_ever_tx

FROM kaia.core.fact_transactions

WHERE tx_succeeded = 'TRUE'

GROUP BY from_address

),

base_data AS (

SELECT

t.from_address AS address,

DATE_TRUNC('month', t.block_timestamp) AS activity_month,

uft.first_ever_tx AS cohort_month,

DATEDIFF('month', uft.first_ever_tx, DATE_TRUNC('month', t.block_timestamp)) AS month_difference

FROM kaia.core.fact_transactions t

JOIN user_first_tx uft ON t.from_address = uft.from_address

WHERE t.tx_succeeded = 'TRUE'

AND uft.first_ever_tx >= '2024-09-01' -- Ensures users started after Kaia Wave

, new_users_count AS (

SELECT

cohort_month,

COUNT(DISTINCT address) AS new_users_count

FROM base_data

GROUP BY cohort_month

, returning_users_count AS (

SELECT

cohort_month,

month_difference,

COUNT(DISTINCT address) AS returning_users_count

FROM base_data

WHERE month_difference > 0