SELECT

t.ACCOUNT,

t.FEE_CHARGED,

TIMESTAMPDIFF(SECOND, CAST(l.BLOCK_TIMESTAMP AS TIMESTAMP_NTZ(6)), CAST(t.CLOSED_AT AS TIMESTAMP_NTZ(6))) AS transaction_time_seconds -- Time taken for transaction

FROM

stellar.core.fact_transactions t

JOIN

stellar.core.fact_ledgers l

ON

t.LEDGER_SEQUENCE = l.SEQUENCE

WHERE

t.CLOSED_AT >= DATEADD(year, -1, CURRENT_DATE())

SELECT

COUNT(*) AS total_transactions,

AVG(transaction_time_seconds) AS avg_transaction_time_seconds,

AVG(FEE_CHARGED) / 10000000.0 AS avg_transaction_cost_xlm, -- Convert from stroops to XLM

AVG(FEE_CHARGED) / 10000000.0 * (SELECT price FROM crosschain.price.ez_prices_hourly WHERE symbol = 'XLM' AND blockchain = 'stellar' ORDER BY hour DESC LIMIT 1) AS avg_transaction_cost_usd

FROM

transaction_data;