flipside

    New and Returning Users
    0xHaM-dNew and Returning Users
    Updated 2025-03-21
    with active_users_u as (
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT FROM_ADDRESS) as active_users
    FROM monad.testnet.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 monad.testnet.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)
    ORDER BY 1 DESC
    Last run: 13 days 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-21 00:00:00.00011205476928813448194742766661.83417638.165824916855.7567574696553.285465972.2567572965797.142
    2
    		2025-03-20 00:00:00.0006572545469893833789066187360771.49343228.506568914065.4657535018681.857462863.9178083076248.714
    3
    		2025-03-19 00:00:00.0005280331332983429090128195049763.06108536.938915835475.4722224600682.714404029.5555562648976
    4
    		2025-03-18 00:00:00.0005534359335095125760294218340860.54813239.451868772871.8732394293992.428362821.0422542439958.142
    5
    		2025-03-17 00:00:00.0004808529294316522409343186536461.2071838.79282704850.6285713785776.714320133.4714292063467.428
    6
    		2025-03-16 00:00:00.0004615020297592819466178163909264.48353435.516466645377.0289863267794282118.5217391668625.142
    7
    		2025-03-15 00:00:00.0004944542276888316490250217565955.99877644.001224586999.9264712793478.857242503.6764711293852
    8
    		2025-03-14 00:00:00.0003375447146604213721367190940543.43252956.567471521961.9850752303028.428204796.522388957369.142
    9
    		2025-03-13 00:00:00.0003646551170802912255325193852246.83957553.160425478727.3636362055213185686.742424826062.142
    10
    		2025-03-12 00:00:00.0003133499186670910547296126679059.57266940.427331429991.6153851748814.285162266.092308651283
    11
    		2025-03-11 00:00:00.00019768497155168680587126133336.19477363.805227387749.31251541274.571135634.171875476781.714
    12
    		2025-03-10 00:00:00.00011826501792697965071100338115.15824684.841754362525.5079371511855.285126429.698413446692.428
    13
    		2025-03-09 00:00:00.0001294814352516778580294229827.22522372.774777349297.6935481537167.857125577.451613464700.857
    14
    		2025-03-08 00:00:00.00015113894135037433286109788627.35913872.640862333797.426231513724.142121857.147541441434.571
    15
    		2025-03-07 00:00:00.00016407395468937019783109384633.33211466.667886314170.91461507116996.383333418060.857
    16
    		2025-03-06 00:00:00.00015017604845756472890101718532.2671467.73286291686.6949151398676.428109710376838.857
    17
    		2025-03-05 00:00:00.00016807216452005988315103552138.38828761.611713270823.3620691391702103246.810345367007.285
    18
    		2025-03-04 00:00:00.00017709145048915343115126602328.51019371.489807246088.3157891452842.57193738.859649452087.714
    19
    		2025-03-03 00:00:00.00013598383053284838224105451022.45326377.546737218859.2857141371810.85786396.857143469719.857
    20
    		2025-03-02 00:00:00.0001130708189652453289694105616.77285483.227146198114.2181821286626.85782416.290909478640.428
    74
    8KB
    42s