SELECT block_timestamp::date AS date

, 'Solana' AS chain

, SUM(CASE WHEN succeeded THEN 1 ELSE 0 END) AS n_tx_successful

, COUNT(1) AS n_tx

, COUNT(DISTINCT block_id) AS n_blocks

FROM solana.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 90

GROUP BY 1

UNION

SELECT block_timestamp::date AS date

, 'Optimism' AS chain

, SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS n_tx_successful

, COUNT(1) AS n_tx

, COUNT(DISTINCT block_hash) AS n_blocks

FROM optimism.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 90

GROUP BY 1

UNION

SELECT block_timestamp::date AS date

, 'Ethereum' AS chain

, SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS n_tx_successful

, COUNT(1) AS n_tx

, COUNT(DISTINCT block_hash) AS n_blocks

FROM ethereum.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 90

GROUP BY 1

SELECT *

, n_blocks / (24 * 60 * 60) AS blocks_per_second

FROM t0

WHERE date < CURRENT_DATE::date