flipside

    New vs Recurring Users
    0xHaM-dNew vs Recurring Users
    Updated 2024-12-05
    with txsTb as (
    SELECT
    BLOCK_TIMESTAMP,
    TX_HASH,
    sender as user
    FROM aptos.core.fact_events a
    JOIN aptos.core.fact_transactions b using(BLOCK_TIMESTAMP,TX_HASH)
    WHERE EVENT_ADDRESS = '0x9770fa9c725cbd97eb50b2be5f7416efdfd1f1554beb0750d4dae4c64e860da3'
    AND EVENT_MODULE = 'controller'
    AND EVENT_RESOURCE ilike ANY ('DepositEvent%','EndFlashLoanEvent%','WithdrawEvent%','BeginFlashLoanEvent%','ClaimRewardEvent%','LiquidateEven%')
    AND TX_TYPE = 'user_transaction'
    )
    ,DAU_u as (
    SELECT
    date_trunc('week', BLOCK_TIMESTAMP) as date,
    count(DISTINCT TX_HASH) as n_txs,
    count(DISTINCT user) as Active_users
    FROM txsTb
    GROUP BY 1
    )
    ,new as (
    SELECT
    date_trunc('week', first_tx) as date,
    count(DISTINCT user) as new_user
    FROM (
    SELECT
    user,
    min(block_timestamp) as first_tx
    FROM txsTb
    GROUP BY 1
    )
    GROUP BY 1
    )

    SELECT
    a.date,
    Last run: 17 days ago
    DATE
    N_TXS
    ACTIVE_USERS
    N_NEW_USER
    CUM_USERS
    RECURRING_USER
    NEW_USER_PERCENT
    RECURRING_USER_PERCENT
    Avg AU
    Avg New User
    CUM_N_TXS
    CUM_NEW_USER
    1
    		2025-05-19 00:00:00.00062122220697697450152331.39639668.60360414749.3561649554.1095893056946697450
    2
    		2025-05-12 00:00:00.000718104591719696753387215.66107684.33892414923.3759677.1253050734696753
    3
    		2025-05-05 00:00:00.0007230285715269603427055.32026694.67973415068.9014089803.2957752978924696034
    4
    		2025-04-28 00:00:00.00011267311616469588229525.26315894.73684215243.3571439941.1714292971694695882
    5
    		2025-04-21 00:00:00.00012165252017069571823506.74603293.25396815419.11594210082.8695652960427695718
    6
    		2025-04-14 00:00:00.0007412269016469554825266.09665493.90334615608.80882410228.6470592948262695548
    7
    		2025-04-07 00:00:00.00012801457319369538443804.22042495.77957615801.62686610378.8656722940850695384
    8
    		2025-03-31 00:00:00.00012196482314069519146832.90275897.09724215971.75757610533.196972928049695191
    9
    		2025-03-24 00:00:00.00031291913285169505182819.31887990.68112116143.27692310693.0923082915853695051
    10
    		2025-03-17 00:00:00.000622044628484694200414410.45808189.54191916252.82812510846.8752884562694200
    11
    		2025-03-10 00:00:00.00012200484927569371645745.67127294.32872816437.34920611011.3650792822358693716
    12
    		2025-03-03 00:00:00.000776432520217278693441792468.55805131.44194916624.25806511184.5322582810158693441
    13
    		2025-02-24 00:00:00.0004423997692453676163731625.11004274.88995816483.63934411084.6393442732515676163
    14
    		2025-02-17 00:00:00.0001989038401164673710267630.312569.687516595.5511228.52688276673710
    15
    		2025-02-10 00:00:00.00044584115549484672546207082.08412717.91587316811.74576311399.0847462668386672546
    16
    		2025-02-03 00:00:00.000437283034432663062260214.23862985.76137116902.39655211432.1034482623802663062
    17
    		2025-01-27 00:00:00.00040743278521366263025727.64811592.35188517145.70175411625.0877192580074662630
    18
    		2025-01-20 00:00:00.00038428577444266241753327.65500592.34499517402.14285711828.8752539331662417
    19
    		2025-01-13 00:00:00.0003614057922664661975312845.99447554.00552517613.56363612035.9090912500903661975
    20
    		2025-01-06 00:00:00.000148993094548659311254617.711782.288317832.48148112209.4629632464763659311
    73
    8KB
    282s