flipside

    New and Returning Users
    0xHaM-dNew and Returning Users
    Updated 2025-03-26
    WITH eventTb AS (
    SELECT
    BLOCK_TIMESTAMP,
    TX_HASH,
    ORIGIN_FROM_ADDRESS as user
    FROM avalanche.defi.ez_dex_swaps
    where platform like 'pharaoh%'
    and year(BLOCK_TIMESTAMP) >= '2025'
    )
    , active_users_u as (
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT user) as active_users
    FROM eventTb
    GROUP BY date
    )
    , new as (
    SELECT
    date_trunc('day', first_tx) as date,
    count(DISTINCT user) as new_user
    FROM (
    SELECT
    user,
    min(block_timestamp) as first_tx
    FROM eventTb
    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,
    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-25 00:00:00.000237831174396206713.07821786.9217833085.6309521942.285885.666667302
    2
    		2025-03-24 00:00:00.000211928474085183513.40254886.5974523094.1566271885.714892.590361306.285
    3
    		2025-03-23 00:00:00.000171223873801147413.90186986.0981313106.048781822.142900.012195305.428
    4
    		2025-03-22 00:00:00.000139323073563116316.51112783.4888733123.2592591804.857908.185185305.142
    5
    		2025-03-21 00:00:00.000175528373333147216.12535683.8746443144.88751830.142916.6625310.285
    6
    		2025-03-20 00:00:00.000218543173050175419.725480.27463162.4810132088924.683544312.857
    7
    		2025-03-19 00:00:00.000205433772619171716.40701183.5929893175.0128212416.428931.012821361.571
    8
    		2025-03-18 00:00:00.000198234172282164117.20484482.7951563189.5714292476.285938.727273369.285
    9
    		2025-03-17 00:00:00.000167427871941139616.6069383.393073205.4605262683.571946.592105381.857
    10
    		2025-03-16 00:00:00.000159123671663135514.83343885.1665623225.882821.714955.506667402.714
    11
    		2025-03-15 00:00:00.000157026671427130416.94267583.0573253247.9729733204.571965.22973433
    12
    		2025-03-14 00:00:00.00035603017116132598.45505691.5449443270.9589043570.571974.808219467.571
    13
    		2025-03-13 00:00:00.000448477270860371217.21677182.7832293266.9444443951.571984.166667659.714
    14
    		2025-03-12 00:00:00.000247339170088208215.81075684.1892443249.8028173968.571987.15493628.142
    15
    		2025-03-11 00:00:00.000343342969697300412.49635987.5036413260.94339.428995.671429694.571
    16
    		2025-03-10 00:00:00.000264142469268221716.05452583.9454753258.40579745311003.884058723.714
    17
    		2025-03-09 00:00:00.000427144868844382310.48934789.5106533267.48529447331012.411765781.285
    18
    		2025-03-08 00:00:00.000413250868396362412.29428887.7057123252.5074634486.8571020.835821774.714
    19
    		2025-03-07 00:00:00.0006227164667888458126.43327473.5667263239.1818184327.1421028.606061761.142
    20
    		2025-03-06 00:00:00.000460355166242405211.97045488.0295463193.2153853998.7141019.107692596.285
    84
    9KB
    4s