SELECT

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS tx_date,

COUNT(DISTINCT TRANSACTION_HASH) AS daily_count

FROM stellar.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= DATEADD(YEAR, -1, CURRENT_DATE)

GROUP BY tx_date

SELECT

AVG(daily_count) AS avg_unique_tx_per_day,

APPROX_PERCENTILE(daily_count, 0.5) AS median_unique_tx_per_day

FROM daily_tx;