datavortexWeekly Dapps rentention
    Updated 2025-01-29
    WITH first_dapp_tx AS (
    SELECT
    from_address,
    MIN(DATE_TRUNC('week', block_timestamp)) AS first_tx_week
    FROM Kaia.core.fact_transactions t
    JOIN Kaia.core.dim_labels d ON t.to_address = d.address
    WHERE d.label_type = 'dapp'
    GROUP BY from_address
    ),

    retention_dapp_users AS (
    SELECT
    COUNT(DISTINCT t.from_address) AS returning_users,
    DATE_TRUNC('week', t.block_timestamp) AS week
    FROM Kaia.core.fact_transactions t
    JOIN first_dapp_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_week
    AND t.block_timestamp >= CURRENT_DATE - INTERVAL '6 months'
    GROUP BY week
    ),

    cohort_size AS (
    SELECT COUNT(DISTINCT from_address) AS cohort_size
    FROM first_dapp_tx
    )

    SELECT
    r.week AS "Week",
    c.cohort_size AS "Cohort Size",
    r.returning_users AS "Returning Users",
    ROUND((r.returning_users * 100.0) / c.cohort_size, 2) AS "Weekly Retention Rate (%)"
    FROM retention_dapp_users r
    JOIN cohort_size c
    ORDER BY r.week;
    QueryRunArchived: QueryRun has been archived