mamad-5XN3k3Stellar 1
    Updated 2025-02-20
    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('day', block_timestamp) as date,
    count(DISTINCT TRANSACTION_HASH) as txs,
    count(DISTINCT ACCOUNT) as users,
    round(sum(FEE_CHARGED)/1e7,0) as fee,
    round(sum(FEE_CHARGED * avg_price)/1e7,0) as fee_usd,
    round(count(case when SUCCESSFUL = 'TRUE' then TRANSACTION_HASH end)/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
    from stellar.core.fact_transactions
    left join pricet on date_trunc('hour', block_timestamp) = pdate
    where block_timestamp::date >= '2025-01-01'
    and block_timestamp::date < Current_date
    group by 1
    order by 1 asc


    Last run: 24 days ago
    DATE
    TXS
    USERS
    FEE
    FEE_USD
    SUCCESS_RATE
    SUCCEEDED
    FAILED
    1
    2025-01-01 00:00:00.0006773199905553713210.24216421845131015
    2
    2025-01-02 00:00:00.000527827211052252272510.32717263603551912
    3
    2025-01-03 00:00:00.0004316350992851384160.39216935992622751
    4
    2025-01-04 00:00:00.0004853472961562092460.33516240633229409
    5
    2025-01-05 00:00:00.00051388291226863550620.3316957973443032
    6
    2025-01-06 00:00:00.00044470341113742220130.40117846722662362
    7
    2025-01-07 00:00:00.00055140861034307329180.33618501203663966
    8
    2025-01-08 00:00:00.0004938188746873138450.33816713963266792
    9
    2025-01-09 00:00:00.0004544047717101655350.32214653283078719
    10
    2025-01-10 00:00:00.0004751256703843481350.31815093583241898
    11
    2025-01-11 00:00:00.00038026096420710756250.39615068832295726
    12
    2025-01-12 00:00:00.0003370056628595249130.4214169061953150
    13
    2025-01-13 00:00:00.0003777798686841446170.39614966702281128
    14
    2025-01-14 00:00:00.0003729082707871661290.3814159242313158
    15
    2025-01-15 00:00:00.0004762176762462471200.35917102213051955
    16
    2025-01-16 00:00:00.0005226706770542510520.30816117383614968
    17
    2025-01-17 00:00:00.0004733119739581716240.32815544023178717
    18
    2025-01-18 00:00:00.0005536234714352378410.28815970993939135
    19
    2025-01-19 00:00:00.0006050120711863036200.2515133414536779
    20
    2025-01-20 00:00:00.0006219675728895612240.27717216184498057
    50
    4KB
    134s