SELECT

DATE_TRUNC('day', block_timestamp) as date,

COUNT(DISTINCT tx_account) as unique_users,

COUNT(*) as total_transactions,

COUNT(CASE WHEN successful = TRUE THEN 1 END) as successful_transactions,

AVG(fee_charged/10000000.0) as avg_fee_xlm, -- Converting from stroop to XLM

SUM(operation_count) as total_operations

FROM stellar.core.fact_transactions

WHERE block_timestamp >= DATEADD('day', -30, CURRENT_DATE)

GROUP BY 1

SELECT

date,

unique_users,

total_transactions,

successful_transactions,

(successful_transactions::FLOAT / NULLIF(total_transactions,0) * 100) as success_rate,

avg_fee_xlm,

total_operations,

total_operations::FLOAT / NULLIF(total_transactions,0) as avg_ops_per_tx

FROM daily_metrics

ORDER BY date DESC;