ArioKaia - Monthly Number of Users by Types
    Updated 2024-10-02
    With all_txs as (
    select
    block_timestamp
    ,from_address as user_address
    from kaia.core.fact_transactions
    where TX_SUCCEEDED = 'TRUE'
    group by 1,2
    ),
    first_txs as (
    select
    user_address
    , min(block_timestamp) as first_timestamp
    from kaia.core.fact_transactions
    inner join (select distinct user_address from all_txs) users
    on fact_transactions.from_address = users.user_address
    where TX_SUCCEEDED = 'TRUE'
    group by 1
    ),
    aggregated as (
    select
    date_trunc('month', block_timestamp) as date
    , iff(date_trunc(month, first_timestamp) = date_trunc(month, block_timestamp), 'New', 'Old') as category
    , count(distinct user_address) as users
    from all_txs
    left join first_txs
    using(user_address)
    group by 1,2
    )
    select *
    from aggregated
    order by date desc, category desc
    QueryRunArchived: QueryRun has been archived