flipside

    New and Active Axie's Users
    0xHaM-dNew and Active Axie's Users
    Updated 2025-03-20
    with qmain as(
    SELECT
    BLOCK_TIMESTAMP,
    TX_HASH,
    FROM_ADDRESS,
    FROM ronin.core.fact_transactions
    WHERE TO_ADDRESS in (
    '0x9d3936dbd9a794ee31ef9f13814233d435bd806c'
    ,'0x32950db2a7164ae833121501c797d79e7b79d74c'
    ,'0x05b0bb3c1c320b280501b86706c3551995bc8571'
    ,'0x3e0674b1ddc84b0cfd9d773bb2ce23fe8f445de3'
    )
    )
    ,
    wallet_age AS (
    SELECT
    FROM_ADDRESS as address,
    count(distinct TX_HASH) as tx_counts,
    count(distinct date(block_timestamp)) as days_active,
    min(date(block_timestamp)) as first_tx,
    max(date(block_timestamp)) as last_tx,
    datediff('day', last_tx, getdate()) as days_last_active,
    datediff('day', first_tx, getdate()) as age_today
    FROM qmain
    WHERE block_timestamp::DATE < current_date()
    AND block_timestamp::DATE >= '2024-01-01'
    group by 1
    )
    ,
    new_users_stat AS (
    SELECT
    first_tx,
    count(distinct address) as new_users,
    count(distinct case when days_active >= 7 AND days_last_active <= 7 then address else null end) as active_user,
    count(distinct case when days_active = 1 then address else null end) as one_day_user,
    count(distinct case when days_active >= 2 then address else null end) as two_or_more_days_user,
    Last run: 15 days ago
    FIRST_TX
    New Wallets
    New Active Users
    Cumulative New Wallets
    Cumulative Active Users
    Active Only 1 Day
    Active >= 2 Day
    Active >= 5 Day
    Active >= 7 Day
    Active >= 14 Day
    Active >= 50 Day
    Active >= 100 Day
    Active >= 200 Day
    1
    		2025-03-19 00:00:00.000770037643913571497518278921240664228260213421123912858230
    2
    		2025-03-18 00:00:00.000653037566913571496748278921240664228260213421123912858230
    3
    		2025-03-17 00:00:00.000906037501613571496218278798240664228260213421123912858230
    4
    		2025-03-16 00:00:00.0005184037411013571495527278583240664228260213421123912858230
    5
    		2025-03-15 00:00:00.0001774036892613571490592278334240664228260213421123912858230
    6
    		2025-03-14 00:00:00.0001257036715213571489207277945240625228260213421123912858230
    7
    		2025-03-13 00:00:00.00012112336589513571488231277664240561228260213421123912858230
    8
    		2025-03-12 00:00:00.0008564036468413569187737276947240466228237213421123912858230
    9
    		2025-03-11 00:00:00.00012818936382813565187199276629240253228197213421123912858230
    10
    		2025-03-10 00:00:00.000208052036254713556286365276182240034228108213421123912858230
    11
    		2025-03-09 00:00:00.000181723336046713504285067275400239482227588213052123912858230
    12
    		2025-03-08 00:00:00.000106629935865013480983613275037239230227355212919123912858230
    13
    		2025-03-07 00:00:00.00092120835758413451083009274575238894227056212737123912858230
    14
    		2025-03-06 00:00:00.000164080535666313430282424274239238673226848212584123912858230
    15
    		2025-03-05 00:00:00.00060836135502313349781848273175237830226043212375123912858230
    16
    		2025-03-04 00:00:00.00079030435441513313681695272720237454225682212034123912858230
    17
    		2025-03-03 00:00:00.000108652235362513283281408272217237092225369211758123912858230
    18
    		2025-03-02 00:00:00.00057022235253913231081067271472236421224839211304123912858230
    19
    		2025-03-01 00:00:00.00079718535196913208880807271162236175224612211093123912858230
    20
    		2025-02-28 00:00:00.00075224635117213190380441270731235972224422210912123912858230
    ...
    170
    16KB
    12s