WITH arbitrum_info AS (

SELECT

'Arbitrum' AS blockchain,

BLOCK_NUMBER,

BLOCK_TIMESTAMP,

FLOOR(DATEDIFF('minute', BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) / 60.0) AS hours,

MOD(DATEDIFF('minute', BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()), 60.0) AS minutes,

CONCAT(IFF(hours < 10, CONCAT('0', hours::STRING), hours::STRING), ':', IFF(minutes < 10, CONCAT('0', minutes::STRING), minutes::STRING)) AS lag,

(hours*60+minutes) AS lag_minutes,

CASE

WHEN lag_minutes <= 60 THEN '🟢 Data is up to date!'

WHEN (lag_minutes > 60 AND lag_minutes <= 90) THEN '🟡 Slightly out of date!'

WHEN lag_minutes > 90 THEN '🔴 Data is out of date!'

END AS status,

'1h' AS update_period

FROM

arbitrum.core.fact_blocks

INNER JOIN arbitrum.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP)

WHERE

BLOCK_TIMESTAMP > CURRENT_DATE() - 3

ORDER BY

BLOCK_TIMESTAMP DESC NULLS LAST

LIMIT 1

),

avalanche_info AS (

SELECT

'Avalanche' AS blockchain,

BLOCK_NUMBER,

BLOCK_TIMESTAMP,

FLOOR(DATEDIFF('minute', BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()) / 60.0) AS hours,

MOD(DATEDIFF('minute', BLOCK_TIMESTAMP, CURRENT_TIMESTAMP()), 60.0) AS minutes,

CONCAT(IFF(hours < 10, CONCAT('0', hours::STRING), hours::STRING), ':', IFF(minutes < 10, CONCAT('0', minutes::STRING), minutes::STRING)) AS lag,