SELECT

from_address,

MIN(block_timestamp) AS first_tx_date

FROM Kaia.core.fact_transactions t

JOIN Kaia.core.dim_labels d ON t.to_address = d.address

WHERE d.label_type = 'games'

GROUP BY from_address

),

retention_game_users AS (

SELECT

COUNT(DISTINCT t.from_address) AS returning_users

FROM Kaia.core.fact_transactions t

JOIN first_game_tx f ON t.from_address = f.from_address

JOIN Kaia.core.dim_labels d ON t.to_address = d.address

WHERE t.block_timestamp >= f.first_tx_date

AND t.block_timestamp >= CURRENT_DATE - INTERVAL '6 months'

),

cohort_size AS (

SELECT COUNT(DISTINCT from_address) AS cohort_size

FROM first_game_tx

SELECT

c.cohort_size AS "Cohort Size",

r.returning_users AS "Returning Users",

ROUND((r.returning_users * 100.0) / c.cohort_size, 2) AS "6-Month Retention Rate (%)"

FROM retention_game_users r

JOIN cohort_size c;