select hour::date as datee,

avg(price) as usdprice

from stellar.price.ez_prices_hourly

where symbol = 'XLM'

group by 1

select date_trunc('day',block_timestamp) as date,

count(DISTINCT transaction_hash) as txs,

count(DISTINCT account) as users,

sum(operation_count) as ops,

count(DISTINCT LEDGER_sequence) as ledgers,

count(DISTINCT case when successful then transaction_hash end) as successful_txs,

count(DISTINCT case when successful='FALSE' then transaction_hash end) as failed_txs,

sum(fee_charged/1e7) as xlm_fees,

sum((fee_charged/1e7)*usdprice) as usd_fees,

from stellar.core.fact_transactions

join price on block_timestamp::date=datee

group by 1