elsinaDistribution of User Transactions
    Updated 2025-04-11
    with price as (
    select
    date_trunc('day', hour) as date,
    avg(price) as price_usd
    from crosschain.price.ez_prices_hourly
    where
    blockchain = 'ethereum' and
    token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    group by 1
    ),

    base as (
    SELECT
    block_timestamp,
    from_address,
    tx_hash,
    value as amount,
    amount * price_usd as amount_usd,
    tx_fee
    from ink.core.fact_transactions left join price on block_timestamp::date = date
    where
    tx_succeeded = TRUE and
    block_timestamp::date >= '2024-12-18'
    ),


    per_user as (
    SELECT
    from_address as user,
    count(distinct tx_hash) as tx_count
    from base
    group by user
    )

    select
    case
    Last run: about 1 month ago
    DIS
    USER_COUNT
    USER_COUNT_PERCENTAGE
    1
    H. more than 12896282.74898
    2
    B. 2-35862216.737713
    3
    E. 16-313883611.088428
    4
    C. 4-7187505.353487
    5
    G. 64-127216006.167217
    6
    A. 14266412.181396
    7
    D. 8-156715719.174621
    8
    F. 32-639298226.548157
    8
    228B
    19s