WITH t0 AS (

SELECT block_id

, block_timestamp

, COUNT(1) AS n_tx_successful

FROM solana.core.fact_transactions

WHERE block_timestamp >= '2020-03-16'

AND succeeded

GROUP BY 1, 2

HAVING COUNT(1) >= 1

), t1 AS (

SELECT t0.*

, ROW_NUMBER() OVER (ORDER BY block_id) AS rn

FROM t0

), t2 AS (

SELECT t1a.*

, DATEDIFF('seconds', t1a.block_timestamp, t1b.block_timestamp) / 60.0 AS m_between_blocks

FROM t1 t1a

JOIN t1 t1b

ON t1b.rn = t1a.rn + 1

), t3 AS (

SELECT *

, CASE WHEN m_between_blocks >= (5) THEN 1 ELSE 0 END AS network_outage

, SUM(network_outage) OVER (ORDER BY block_timestamp) AS cumu_network_outages

FROM t2

), t4 AS (

SELECT cumu_network_outages

, MIN(block_timestamp) AS mn_time

FROM t3

GROUP BY 1

), t5 AS (

SELECT t3.*

, DATEDIFF('days', mn_time, block_timestamp) AS days_since_last_outage

FROM t3

JOIN t4 ON t4.cumu_network_outages = t3.cumu_network_outages