SELECT

FROM_ADDRESS,

MIN(BLOCK_TIMESTAMP) AS first_tx_timestamp

FROM

boba.core.fact_traces

WHERE

BLOCK_TIMESTAMP >= DATEADD('year', -1, CURRENT_DATE)

GROUP BY

FROM_ADDRESS

),

user_retention AS (

SELECT

fs.FROM_ADDRESS,

DATE_TRUNC('day', fs.first_tx_timestamp) AS cohort_day,

COUNT(DISTINCT DATE_TRUNC('day', t.BLOCK_TIMESTAMP)) AS active_days,

DATEDIFF(

'day',

fs.first_tx_timestamp,

MAX(t.BLOCK_TIMESTAMP)

) AS days_retained

FROM

first_seen fs

JOIN boba.core.fact_traces t ON fs.FROM_ADDRESS = t.FROM_ADDRESS

GROUP BY

fs.FROM_ADDRESS,

fs.first_tx_timestamp

cohort_day,

COUNT(DISTINCT from_address) AS cohort_size,

COUNT(

DISTINCT CASE

WHEN days_retained >= 1 THEN from_address

END

) AS d1_retained,