developer_violaKaia Retention
    Updated 2025-03-21
    WITH users AS (
    SELECT
    from_address AS user,
    MIN(DATE_TRUNC('month', block_timestamp)) AS month_cohort,
    MIN(DATE_TRUNC('day', block_timestamp)) AS first_month,
    COUNT(DISTINCT block_timestamp) AS distinct_days_active,
    MAX(DATE_TRUNC('day', block_timestamp)) AS last_month_active,
    COUNT(DISTINCT to_address) AS distinct_interactions
    FROM kaia.core.fact_transactions
    WHERE tx_succeeded = TRUE
    GROUP BY 1
    HAVING MIN(DATE_TRUNC('month', block_timestamp)) >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 month')
    )

    SELECT
    month_cohort,
    COUNT(DISTINCT user) AS total_users,
    ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '1 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_1,
    ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '2 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_2,
    ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '3 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_3,
    ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '4 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_4,
    ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '5 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_5,
    ROUND(COUNT(DISTINCT CASE WHEN last_month_active >= first_month + INTERVAL '6 month' THEN user END) * 100.0 / COUNT(DISTINCT user), 2) AS retention_rate_6
    FROM users
    WHERE month_cohort >= DATE('2024-08-29')
    GROUP BY month_cohort
    ORDER BY month_cohort;


    QueryRunArchived: QueryRun has been archived