WITH t0 AS (

SELECT DATE_TRUNC('second', block_timestamp) AS second

, 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

FROM solana.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 90

GROUP BY 1, 2

UNION

SELECT DATE_TRUNC('second', block_timestamp) AS second

, block_timestamp::date AS date

, 'Polygon' AS chain

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

, COUNT(1) AS n_tx

FROM polygon.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 90

GROUP BY 1, 2

UNION

SELECT DATE_TRUNC('second', block_timestamp) AS second

, 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

FROM ethereum.core.fact_transactions

WHERE block_timestamp >= CURRENT_DATE - 90

GROUP BY 1, 2

), t1 AS (

SELECT *

, ROW_NUMBER() OVER (PARTITION BY chain, date ORDER BY n_tx_successful DESC) AS rn

FROM t0

SELECT *

FROM t1

WHERE rn = 1