mamad-5XN3k3Stellar 2
    Updated 2025-02-28
    with pricet as (
    select
    HOUR::date as pdate,
    avg(PRICE) as avg_price
    from crosschain.price.ez_prices_hourly
    where symbol = 'XLM'
    and hour::date >= '2025-01-01'
    group by 1
    )

    select
    --date_trunc('hour', block_timestamp) as date,
    count(DISTINCT TRANSACTION_HASH) as txs,
    count(DISTINCT ACCOUNT) as users,
    sum(FEE_CHARGED)/1e7 as fee,
    avg(FEE_CHARGED)/1e7 as avg_fees,
    sum(FEE_CHARGED * avg_price)/1e7 as fee_usd,
    avg(FEE_CHARGED * avg_price)/1e7 as avg_fees_usd,
    round(count(case when SUCCESSFUL = 'TRUE' then TRANSACTION_HASH end)*100/count(DISTINCT TRANSACTION_HASH),3) as success_rate,
    count(case when SUCCESSFUL = 'TRUE' then TRANSACTION_HASH end) as succeeded,
    count(case when SUCCESSFUL != 'TRUE' then TRANSACTION_HASH end) as failed,
    min(BLOCK_TIMESTAMP) as min_time,
    max(BLOCK_TIMESTAMP) as max_time,
    count(distinct BLOCK_TIMESTAMP::date) as days,
    count(DISTINCT TRANSACTION_HASH)/count(distinct BLOCK_TIMESTAMP::date) as avg_txs,
    count(DISTINCT ACCOUNT)/count(distinct BLOCK_TIMESTAMP::date) as avg_users,
    (sum(FEE_CHARGED)/1e7)/count(distinct BLOCK_TIMESTAMP::date) as avg_fee,
    (sum(FEE_CHARGED)/1e7)/count(DISTINCT TRANSACTION_HASH) as avg_fee_per_tx,
    (sum(FEE_CHARGED * avg_price)/1e7)/count(distinct BLOCK_TIMESTAMP::date) as avg_fee_usd,
    (sum(FEE_CHARGED * avg_price)/1e7)/count(DISTINCT TRANSACTION_HASH) as avg_fee_per_tx_usd
    from stellar.core.fact_transactions
    left join pricet on date_trunc('hour', block_timestamp) = pdate
    where block_timestamp::date >= '2025-01-01'
    --where transaction_hash = '4c269995eefc7185d40100a8d990ee42ef30e6588ddfef15a58182e6bc2f9322'
    --where block_timestamp >= '2024-12-17 11:00:00.000'
    --group by 1
    Last run: 16 days ago
    TXS
    USERS
    FEE
    AVG_FEES
    FEE_USD
    AVG_FEES_USD
    SUCCESS_RATE
    SUCCEEDED
    FAILED
    MIN_TIME
    MAX_TIME
    DAYS
    AVG_TXS
    AVG_USERS
    AVG_FEE
    AVG_FEE_PER_TX
    AVG_FEE_USD
    AVG_FEE_PER_TX_USD
    1
    269279554578207159360.3418110.0005918026061963.1857713080.000167937062437.118999501861693293682025-01-01 00:00:02.0002025-02-28 12:09:58.000594564060.2372889800.1186442701.0227425593220.00059180260633.2743351070.00000729051182
    1
    251B
    19s