flipside

    Kaia: Churn Rate
    i_danKaia: Churn Rate
    Updated 2025-02-26
    WITH last_activity AS ( -- Get the last quarter each user was active
    SELECT
    from_address
    , MAX(DATE_TRUNC('quarter', block_timestamp)) AS last_active_quarter
    FROM kaia.core.fact_transactions
    GROUP BY 1
    ),
    all_users AS ( -- Get all users who were active in each quarter
    SELECT
    DATE_TRUNC('quarter', block_timestamp) AS active_quarter
    , from_address
    FROM kaia.core.fact_transactions
    ),
    churned_users AS ( -- Users who were last active in a quarter and never returned
    SELECT
    l.last_active_quarter AS churned_quarter
    , COUNT(DISTINCT l.from_address) AS churned_users
    FROM last_activity l
    LEFT JOIN all_users a
    ON l.from_address = a.from_address
    AND a.active_quarter > l.last_active_quarter -- Check if they were active later
    WHERE a.from_address IS NULL -- If NULL, they never returned
    GROUP BY 1
    )
    -- Get total users per quarter and join with churned users
    SELECT
    a.active_quarter AS "Quarter"
    , COUNT(DISTINCT a.from_address) AS "Total Users"
    , COALESCE(c.churned_users, 0) AS "Churned Users"
    FROM all_users a
    LEFT JOIN churned_users c
    ON a.active_quarter = c.churned_quarter
    WHERE a.active_quarter < DATE_TRUNC('quarter', CURRENT_DATE) -- Exclude the current quarter
    GROUP BY 1, 3
    ORDER BY 1


    Last run: about 1 month ago
    Quarter
    Total Users
    Churned Users
    1
    		2019-04-01 00:00:00.000495189
    2
    		2019-07-01 00:00:00.000631278611563
    3
    		2019-10-01 00:00:00.00042087284076834
    4
    		2020-01-01 00:00:00.00029234052786759
    5
    		2020-04-01 00:00:00.00017973131705931
    6
    		2020-07-01 00:00:00.000780787701451
    7
    		2020-10-01 00:00:00.000643030519061
    8
    		2021-01-01 00:00:00.000529851429405
    9
    		2021-04-01 00:00:00.000615045491135
    10
    		2021-07-01 00:00:00.000597934292831
    11
    		2021-10-01 00:00:00.00027145971918774
    12
    		2022-01-01 00:00:00.00023171531640620
    13
    		2022-04-01 00:00:00.00025096862089977
    14
    		2022-07-01 00:00:00.0001171352941462
    15
    		2022-10-01 00:00:00.000991872818225
    16
    		2023-01-01 00:00:00.0001268955836799
    17
    		2023-04-01 00:00:00.00040940423812260
    18
    		2023-07-01 00:00:00.00023913722162316
    19
    		2023-10-01 00:00:00.00023628452091948
    20
    		2024-01-01 00:00:00.00052749215014470
    23
    989B
    239s