saeedmzn[Avalanche active users (2024)] - Total
    Updated 2024-05-16
    with txns as (
    select BLOCK_TIMESTAMP::date date ,
    FROM_ADDRESS user ,
    TX_HASH ,
    TX_FEE
    from avalanche.core.fact_transactions
    where BLOCK_TIMESTAMP::date >= '2024-01-01'
    and status = 'SUCCESS'
    ),
    avax_price as (
    select date_trunc ('day',HOUR)::Date date ,
    median(price) price
    from avalanche.price.ez_prices_hourly
    where SYMBOL ='AVAX'
    and date_trunc ('day',HOUR)::Date >= '2024-01-01'
    group by 1
    ),
    final as (
    select
    date ,
    user ,
    TX_HASH ,
    TX_FEE ,
    TX_FEE * price TX_FEE_USD
    from txns left join avax_price using (date)
    )
    select count (tx_hash) num_transactions ,
    count (DISTINCT user ) num_users ,
    sum (TX_FEE) paid_fees_amount,
    sum (TX_FEE_USD) paid_fees_amount_USD,
    num_transactions / num_users avg_transactions_per_user,
    paid_fees_amount / num_users "avg paid fees per wallet"
    from final

    QueryRunArchived: QueryRun has been archived