gigiokobamusical-teal
    Updated 2025-02-09
    WITH daily_users AS (
    SELECT
    DATE_TRUNC('week', block_timestamp) AS activity_date,
    origin_from_address AS user_address
    FROM avalanche.core.ez_decoded_event_logs
    WHERE contract_address = '0xaaab9d12a30504559b0c5a9a5977fee4a6081c6b'
    GROUP BY 1, 2
    ),
    previous_day AS (
    SELECT
    activity_date,
    user_address,
    LAG(activity_date) OVER (PARTITION BY user_address ORDER BY activity_date) AS prev_active_date
    FROM daily_users
    ),
    churned_users AS (
    SELECT
    activity_date,
    user_address
    FROM previous_day
    WHERE prev_active_date IS NOT NULL
    AND prev_active_date < activity_date - INTERVAL '7 day' -- User was active before but skipped at least one day
    )
    SELECT
    activity_date,
    COUNT(DISTINCT user_address) AS churned_user_count,
    (COUNT(DISTINCT user_address)::FLOAT / NULLIF((SELECT COUNT(DISTINCT user_address) FROM daily_users d WHERE d.activity_date = churned_users.activity_date - INTERVAL '7 day'), 0)) * 100 AS churn_rate
    FROM churned_users
    GROUP BY 1
    ORDER BY 1 DESC;

    Last run: 14 days ago
    ACTIVITY_DATE
    CHURNED_USER_COUNT
    CHURN_RATE
    1
    2025-02-03 00:00:00.00028312.032312925
    2
    2025-01-27 00:00:00.00046425.108225108
    3
    2025-01-20 00:00:00.00050153.928955867
    4
    2025-01-13 00:00:00.00025931.896551724
    5
    2025-01-06 00:00:00.00018222.114216282
    6
    2024-12-30 00:00:00.00026236.592178771
    7
    2024-12-23 00:00:00.00014515.743756786
    8
    2024-12-16 00:00:00.00027329.323308271
    9
    2024-12-09 00:00:00.00025520.935960591
    10
    2024-12-02 00:00:00.00038636.761904762
    11
    2024-11-25 00:00:00.00037944.958481613
    12
    2024-11-18 00:00:00.00028635.929648241
    13
    2024-11-11 00:00:00.00028329.60251046
    14
    2024-11-04 00:00:00.00036058.536585366
    15
    2024-10-28 00:00:00.00017938.085106383
    16
    2024-10-21 00:00:00.00014128.031809145
    17
    2024-10-14 00:00:00.00016430.314232902
    18
    2024-10-07 00:00:00.00016111.899482631
    19
    2024-09-30 00:00:00.0001010178.130511464
    20
    2024-09-23 00:00:00.00019141.52173913
    59
    3KB
    7s