-- All time calculations

COUNT(*) AS total_transactions

, COUNT(DISTINCT tx_signer) AS Total_Users

, SUM(transaction_fee/1e24) AS Total_fees

, DATEDIFF('second'

, MIN(block_timestamp)

, MAX(block_timestamp)) AS total_seconds

, COUNT(*) / NULLIF(DATEDIFF('second'

, MIN(block_timestamp)

, MAX(block_timestamp))

, 0) AS avg_all_time_tps

-- Last 30 days calculations

, COUNT(CASE

WHEN block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

THEN 1 END) AS last_30_days_transactions

, COUNT(DISTINCT CASE

WHEN block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

THEN tx_signer END) AS last_30_days_users

, SUM(CASE

WHEN block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

THEN transaction_fee/1e24 ELSE 0 END) AS last_30_days_tx_fees

, DATEDIFF('second'

, MIN(CASE

WHEN block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

THEN block_timestamp END)

, MAX(block_timestamp)) AS last_30_days_seconds

, COUNT(CASE

WHEN block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

THEN 1 END) / NULLIF(DATEDIFF('second'

, MIN(CASE

WHEN block_timestamp >= CURRENT_DATE - INTERVAL '30 days'

THEN block_timestamp END)

, MAX(block_timestamp))

, 0) AS avg_last_30_days_tps