cybergenlab[CEX Overview] New User metric
    Updated 2024-11-18
    --Get historical DeFi project user growth

    with monthly_new_users as (
    select
    date_trunc('month', block_timestamp) as time,
    label_type as category,
    count(distinct from_address) as new_users,
    from base.core.fact_transactions as transactions
    left join base.core.dim_labels labels on transactions.to_address = labels.address
    where block_timestamp < date_trunc('month',current_date())
    --block_timestamp >= dateadd(year, -3, date_trunc('month',current_date()))
    and nonce = 1
    and category in ('cex')
    and status = 'SUCCESS'
    group by 1,2
    )

    select
    time,
    new_users
    from monthly_new_users
    order by 1 desc
    limit 1


    Last run: about 2 months ago
    TIME
    NEW_USERS
    1
    2024-10-01 00:00:00.00010153
    1
    35B
    160s