flipside

    New and Returning Users Avalanche[Post Upgrade]
    0xHaM-dNew and Returning Users Avalanche[Post Upgrade]
    Updated 2025-03-27
    with active_users_u as (
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT FROM_ADDRESS) as active_users
    FROM avalanche.core.fact_transactions
    GROUP BY date
    )
    , new as (
    SELECT
    date_trunc('day', first_tx) as date,
    count(DISTINCT user) as new_user
    FROM (
    SELECT
    FROM_ADDRESS as user,
    min(block_timestamp) as first_tx
    FROM avalanche.core.fact_transactions
    GROUP BY 1
    )
    GROUP BY 1
    )

    SELECT
    a.date,
    active_users,
    coalesce(new_user,0) as n_new_user,
    sum(n_new_user) over (ORDER by date) as cum_users,
    active_users - n_new_user as recurring_user,
    100 * n_new_user / active_users as new_user_percent,
    100 * recurring_user / active_users as recurring_user_percent,
    avg(active_users)over(ORDER BY date) as "Avg Active Users",
    AVG(active_users) over (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avarage_7_active_users,
    avg(n_new_user)over(ORDER BY date) as "Avg New User",
    AVG(n_new_user) over (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as avarage_7_new_user
    FROM active_users_u a
    LEFT JOIN new b using(date)
    where a.date > '2024-12-16 17:00:00.000'
    Last run: 3 months ago
    DATE
    ACTIVE_USERS
    N_NEW_USER
    CUM_USERS
    RECURRING_USER
    NEW_USER_PERCENT
    RECURRING_USER_PERCENT
    Avg Active Users
    AVARAGE_7_ACTIVE_USERS
    Avg New User
    AVARAGE_7_NEW_USER
    1
    		2025-03-27 00:00:00.0005111921631626401489564.23130395.76869752405.64356466080.28516102.9801988312.142
    2
    		2025-03-26 00:00:00.0001668751030316242381565726.17408293.82591852418.5163464.71416242.388959.285
    3
    		2025-03-25 00:00:00.00011483814136161393510070212.30951487.69048651262.38383844191.85716302.3737378178.142
    4
    		2025-03-24 00:00:00.000377041041315997992729127.61775972.38224150613.65306134287.28516324.4795928098
    5
    		2025-03-23 00:00:00.00028856469815893862415816.28084383.71915750746.74226837143.57116385.422688994.428
    6
    		2025-03-22 00:00:00.000315361032715846882120932.74670267.25329850974.77083339442.85716507.16666710026.285
    7
    		2025-03-21 00:00:00.00031634614515743612548919.42530280.57469851179.38947438956.57116572.2210539150.428
    8
    		2025-03-20 00:00:00.00032810669315682162611720.39926979.60073151387.31914939540.28516683.1489369436.142
    9
    		2025-03-19 00:00:00.00031965483515615232713015.12591984.87408151587.07526940747.14216790.5698929784.285
    10
    		2025-03-18 00:00:00.000455061357515566883193129.83123170.16876951800.35869643222.85716920.52173910049.285
    11
    		2025-03-17 00:00:00.000576981668815431134101028.92301371.07698751869.52747345613.85716957.2857149768.285
    12
    		2025-03-16 00:00:00.000449511192115264253303026.51998873.48001251804.76666747165.57116960.27777812056.857
    13
    		2025-03-15 00:00:00.00028132419615145042393614.91539985.08460151881.77528146426.57117016.89887611705.857
    14
    		2025-03-14 00:00:00.00035720814515103082757522.80235277.19764852151.65909146856.14217162.59090911671.571
    15
    		2025-03-13 00:00:00.00041258913015021633212822.12904277.87095852340.52873647424.28517266.24137911282.428
    16
    		2025-03-12 00:00:00.00049295669014930334260513.57135686.42864452469.39534947479.71417360.84883710907.428
    17
    		2025-03-11 00:00:00.000622431160814863435063518.64948781.35051352506.74117646815.57117486.38823511094
    18
    		2025-03-10 00:00:00.000685603270814747353585247.70711852.29288252390.83333344897.57117556.36904810497
    19
    		2025-03-09 00:00:00.00039778946414420273031423.79204676.20795452196.02409640720.85717373.8192776566.142
    20
    		2025-03-08 00:00:00.00031139395614325632718312.70432687.29567452347.46341541993.28517470.2804885940.285
    ...
    101
    11KB
    21s