fantasySEI User Retention & Growth Analysis
    Updated 2025-05-01
    WITH user_activity AS (
    SELECT
    Tx_from,
    DATE_TRUNC('month', block_timestamp) AS DATE
    FROM
    sei.core.fact_transactions
    ),
    first_time_users AS (
    SELECT
    Tx_from,
    MIN(DATE_TRUNC('month', block_timestamp)) AS first_active_month
    FROM
    sei.core.fact_transactions
    GROUP BY
    Tx_from
    ),
    monthly_stats AS (
    SELECT
    ua.DATE,
    COUNT(DISTINCT ua.tx_from) AS monthly_active_users,
    COUNT(
    DISTINCT CASE
    WHEN ftu.first_active_month = ua.DATE THEN ua.tx_from
    END
    ) AS new_users,
    COUNT(
    DISTINCT CASE
    WHEN ftu.first_active_month < ua.DATE THEN ua.tx_from
    END
    ) AS returning_users
    FROM
    user_activity ua
    JOIN first_time_users ftu ON ua.tx_from = ftu.tx_from
    GROUP BY
    ua.DATE
    ),
    Last run: 30 days ago
    DATE
    MAU
    GROWTH_RATE
    NEW_USERS
    NEW_USERS_PERCENTAGE
    RETURNING_USERS
    RETURNING_USERS_RATE
    RETENTION_RATE
    CHURN_RATE
    1
    2023-05-01 00:00:00.000888810000
    2
    2023-06-01 00:00:00.000935.6818182830.1075276569.89247373.86363626.1364
    3
    2023-07-01 00:00:00.00010310.7526884139.8058256260.19417566.66666733.3333
    4
    2023-08-01 00:00:00.000405529393617.47572840539899.9676971310.032303127.184466-27.1845
    5
    2023-09-01 00:00:00.000102441-74.7389219211489.9190751032710.0809252.5465597.4534
    6
    2023-10-01 00:00:00.00018011-82.4181721148463.761035652736.2389656.37147293.6285
    7
    2023-11-01 00:00:00.0003063470.084948755024.6458182308475.354182128.166121-28.1661
    8
    2023-12-01 00:00:00.000120023291.7966968897274.1291253105125.870875101.361233-1.3612
    9
    2024-01-01 00:00:00.000321298167.69702525294578.725986835321.2740256.94991843.0501
    10
    2024-02-01 00:00:00.000270389-15.84479215558357.54043211480642.45956835.73193764.2681
    11
    2024-03-01 00:00:00.000147987-45.2688535018133.909069780666.0909436.17232963.8277
    12
    2024-04-01 00:00:00.00077668-47.5170121884824.2673955882075.73260539.74673560.2533
    13
    2024-05-01 00:00:00.00074090-4.6067881983526.7714945425573.22850669.85502430.145
    14
    2024-06-01 00:00:00.00070350-5.0479153558050.5756933477049.42430746.9294153.0706
    15
    2024-07-01 00:00:00.00062777-10.7647482501639.8489893776160.15101153.67590646.3241
    16
    2024-08-01 00:00:00.000394578528.53911536180091.692897327788.30710352.2133947.7866
    17
    2024-09-01 00:00:00.00073916187.32950168598092.805221531817.19477913.47794486.5221
    18
    2024-10-01 00:00:00.0007697004.13157670118691.09861685148.901399.26915890.7308
    19
    2024-11-01 00:00:00.0001795104133.221255160204989.2454719305510.7545325.0818574.9181
    20
    2024-12-01 00:00:00.0005400150200.826582474331787.83676465683312.16323636.59024863.4098
    25
    2KB
    283s