Haisenbergchurn-rate
    Updated 2025-03-29
    -- forked from retention-rate @ https://flipsidecrypto.xyz/studio/queries/d3cc3b28-cea5-4d16-af81-478166c17e9e

    -- Step 1: Create CTEs to identify active users for each month

    WITH monthly_active_users AS (
    SELECT DISTINCT
    date_trunc('month', block_timestamp) AS month,
    sender AS address
    FROM
    aptos.core.fact_transactions
    WHERE
    block_timestamp >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '13 months'
    AND block_timestamp::date >= '{{Start_Date}}' AND block_timestamp::date <= '{{End_Date}}'
    ),

    -- Step 2: Count active users per month
    monthly_user_counts AS (
    SELECT
    month,
    COUNT(DISTINCT address) AS active_users
    FROM
    monthly_active_users
    GROUP BY
    month
    ),

    -- Step 3: Identify retained users
    retained_users AS (
    SELECT
    a.month,
    COUNT(DISTINCT a.address) AS retained_count
    FROM
    monthly_active_users a
    JOIN
    monthly_active_users b ON a.address = b.address AND a.month = b.month + INTERVAL '1 month'
    GROUP BY
    Last run: 2 months ago
    MONTH
    CURRENT_USERS
    PREVIOUS_USERS
    CHURNED_USERS
    CHURN_RATE
    1
    2024-02-01 00:00:00.0001565952
    2
    2024-03-01 00:00:00.00025073471565952106073767.737517
    3
    2024-04-01 00:00:00.00018827742507347198907079.329666
    4
    2024-05-01 00:00:00.00016073221882774157370583.584381
    5
    2024-06-01 00:00:00.00039225041607322117289472.971937
    6
    2024-07-01 00:00:00.00027037603922504332130084.672954
    7
    2024-08-01 00:00:00.00030439292703760198321573.35026
    8
    2024-09-01 00:00:00.00040319953043929210088169.018725
    9
    2024-10-01 00:00:00.00081176924031995266751366.158639
    10
    2024-11-01 00:00:00.00082634388117692519516163.998006
    11
    2024-12-01 00:00:00.000103538498263438487385958.981008
    12
    2025-01-01 00:00:00.00094679910353849963619693.068732
    12
    734B
    79s