0xHaM-dNew and Returning Users APTOS[Last 30Days]
    Updated 2025-01-07
    -- forked from New and Returning Users APTOS @ https://flipsidecrypto.xyz/edit/queries/87b82907-93b1-4705-9a95-69eab48d35c2
    with tb1 as (
    select
    c.block_timestamp,
    gas_used,
    INITCAP(LABEL_TYPE) as Type,
    iff(INITCAP(LABEL)='Move Dollar','Thala',INITCAP(LABEL)) as LABEL,
    address_name,
    c.block_number,
    c.tx_hash,
    sender,
    ((gas_used * gas_unit_price)/1e8) as amount
    from aptos.core.fact_events a
    join aptos.core.dim_labels b on address = event_address
    join aptos.core.fact_transactions c using(tx_hash)
    where SUCCESS=true
    and tx_type = 'user_transaction'
    )
    , active_users_u as (
    SELECT
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT sender) as active_users
    FROM tb1
    GROUP BY date
    )
    ,new as (
    SELECT
    date_trunc('day', first_tx) as date,
    count(DISTINCT user) as new_user
    FROM (
    SELECT
    sender as user,
    min(block_timestamp) as first_tx
    FROM tb1
    GROUP BY 1)
    GROUP BY 1
    QueryRunArchived: QueryRun has been archived