flipside

    active users ink returning
    ivyk-WIGuQoactive users ink returning
    Updated 2025-02-24
    with active_users_u as (
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT FROM_ADDRESS) as active_users
    FROM ink.core.ez_native_transfers
    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 ink.core.ez_native_transfers
    GROUP BY 1
    )
    GROUP BY 1
    )

    SELECT
    a.date,
    active_users,
    coalesce(new_user,0) as new_users,
    sum(new_users) over (ORDER by date) as cum_users,
    active_users - new_users as recurring_user,
    100 * new_users / 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(new_users)over(ORDER BY date) as "Avg New User",
    AVG(new_users) 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: 2 months ago
    DATE
    ACTIVE_USERS
    NEW_USERS
    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-02-24 00:00:00.00088421158219800768413.09658486.9034167834.62510241.5712747.51687.142
    2
    		2025-02-23 00:00:00.0001302820952186421093316.08074983.9192517821.87341810446.7142767.6202531715.285
    3
    		2025-02-22 00:00:00.00099401569216547837115.78470884.2152927755.12820510910.5712776.243591684.571
    4
    		2025-02-21 00:00:00.00081001523214978657718.80246981.1975317726.75324711057.2852791.9220781653.857
    5
    		2025-02-20 00:00:00.00096651862213455780319.26539180.7346097721.84210511008.8572808.6184211603.571
    6
    		2025-02-19 00:00:00.00094301544211593788616.37327783.6267237695.93333310879.2852821.241536
    7
    		2025-02-18 00:00:00.0001268620592100491062716.2304983.769517672.510797.7142838.51501.714
    8
    		2025-02-17 00:00:00.000102781355207990892313.18349986.8165017603.82191810311.7142849.1780821390.428
    9
    		2025-02-16 00:00:00.0001627518802066351439511.55145988.4485417566.68055699912869.9305561375.428
    10
    		2025-02-15 00:00:00.000109671354204755961312.34612987.6538717444.0281699057.5712883.8732391307.714
    11
    		2025-02-14 00:00:00.00077611171203401659015.08826284.9117387393.788642905.7285711316.571
    12
    		2025-02-13 00:00:00.00087581389202230736915.85978584.1402157388.37681288172930.8695651364.428
    13
    		2025-02-12 00:00:00.00088591304200841755514.71949485.2805067368.2352948618.7142953.5441181357.428
    14
    		2025-02-11 00:00:00.00092841280199537800413.78716186.2128397345.9850758598.4282978.1641791362.857
    15
    		2025-02-10 00:00:00.00080331250198257678315.56081284.4391887316.6212128636.2853003.8939391393.142
    16
    		2025-02-09 00:00:00.00097411406197007833514.43383685.5661647305.68493.1423030.8769231383.857
    17
    		2025-02-08 00:00:00.00096121416195601819614.73158685.2684147267.5468758208.5713056.2656251450.285
    18
    		2025-02-07 00:00:00.00074321506194185592620.26372479.7362767230.3333338116.1423082.3015871793
    19
    		2025-02-06 00:00:00.00073701340192679603018.18181881.8181827227.0806458399.4283107.7258061964.428
    20
    		2025-02-05 00:00:00.00087171342191339737515.39520584.6047957224.7377059146.8573136.7049182173.142
    80
    8KB
    33s