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