SELECT

FROM_ADDRESS AS user,

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,

COUNT(DISTINCT TX_HASH) AS txs

FROM ink.core.fact_transactions

WHERE

block_timestamp >= current_date - INTERVAL '30 Day'

AND TX_SUCCEEDED = TRUE

GROUP BY user, day

),

user_days AS (

-- Count the number of active days per user

SELECT

user,

COUNT(day) AS active_days

FROM user_activity

GROUP BY user

SELECT

COUNT(DISTINCT user) AS total_users,

COUNT(DISTINCT CASE WHEN active_days > 1 THEN user END) AS repeat_users,

ROUND((COUNT(DISTINCT CASE WHEN active_days > 1 THEN user END)::FLOAT / NULLIF(COUNT(DISTINCT user), 0)) * 100, 2) AS retention_rate

FROM user_days

;