ArioKaia - Users Stats 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
    category,
    min(users) as minimum,
    round(avg(users), 0) as average,
    median(users) as median,
    max(users) as maximum
    from
    aggregated
    QueryRunArchived: QueryRun has been archived