-- forked from piper / Blockchain Data Status @ https://flipsidecrypto.xyz/piper/q/qYzMP01XWaaN/blockchain-data-status -- Thanks to @marqu, forked from https://flipsidecrypto.xyz/marqu/q/enMpSjzqEmLn/solana-nfts---last-update-info. and modified it! 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, (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 avalanche.core.fact_blocks INNER JOIN avalanche.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), base_info AS ( SELECT 'Base' 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 base.core.fact_blocks INNER JOIN base.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), bitcoin_info AS ( SELECT 'Bitcoin' 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 <= 90 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 90 AND lag_minutes <= 180) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 180 THEN '🔴 Data is out of date!' END AS status, '1h' AS update_period FROM bitcoin.core.fact_blocks INNER JOIN bitcoin.core.fact_transactions USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), bsc_info AS ( SELECT 'BSC' 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 bsc.core.fact_blocks INNER JOIN bsc.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), ethereum_info AS ( SELECT 'Ethereum' 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 ethereum.core.fact_blocks INNER JOIN ethereum.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), gnosis_info AS ( SELECT 'Gnosis' 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 <= 120 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 120 AND lag_minutes <= 150) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 150 THEN '🔴 Data is out of date!' END AS status, '2h' AS update_period FROM gnosis.core.fact_blocks INNER JOIN gnosis.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), optimism_info AS ( SELECT 'Optimism' 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 optimism.core.fact_blocks INNER JOIN optimism.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), polygon_info AS ( SELECT 'Polygon' 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 polygon.core.fact_blocks INNER JOIN polygon.core.fact_event_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), aurora_info AS ( SELECT 'Aurora' 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 aurora.core.fact_blocks INNER JOIN aurora.core.fact_logs USING(BLOCK_NUMBER, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 7 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), axelar_info AS ( SELECT 'Axelar' AS blockchain, BLOCK_ID AS 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 <= 120) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 120 THEN '🔴 Data is out of date!' END AS status, '1h' AS update_period FROM axelar.core.fact_blocks INNER JOIN axelar.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), cosmos_info AS ( SELECT 'Cosmos' AS blockchain, BLOCK_ID AS 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 <= 1440 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 1440 AND lag_minutes <= 1560) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 1560 THEN '🔴 Data is out of date!' END AS status, '24h' AS update_period FROM cosmos.core.fact_blocks INNER JOIN cosmos.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), /*evmos_info AS ( SELECT 'Evmos' AS blockchain, BLOCK_ID AS 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 <= 720 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 720 AND lag_minutes <= 840) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 840 THEN '🔴 Data is out of date!' END AS status, '12h' AS update_period FROM evmos.core.fact_blocks INNER JOIN evmos.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ),*/ flow_info AS ( SELECT 'Flow' AS blockchain, BLOCK_HEIGHT AS 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 flow.core.fact_blocks INNER JOIN flow.core.fact_events USING(BLOCK_HEIGHT, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), near_info AS ( SELECT 'Near' AS blockchain, BLOCK_ID AS 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 <= 720 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 720 AND lag_minutes <= 840) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 840 THEN '🔴 Data is out of date!' END AS status, '12h' AS update_period FROM near.core.fact_blocks INNER JOIN near.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), osmosis_info AS ( SELECT 'Osmosis' AS blockchain, BLOCK_ID AS 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 <= 720 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 720 AND lag_minutes <= 840) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 840 THEN '🔴 Data is out of date!' END AS status, '12h' AS update_period FROM osmosis.core.fact_blocks INNER JOIN osmosis.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), sei_info AS ( SELECT 'Sei' AS blockchain, BLOCK_ID AS 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 sei.core.fact_blocks INNER JOIN sei.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), solana_info AS ( SELECT 'Solana' AS blockchain, BLOCK_ID AS 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 solana.core.fact_blocks INNER JOIN solana.core.fact_events USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), terra_info AS ( SELECT 'Terra' AS blockchain, BLOCK_ID AS 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 <= 1440 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 1440 AND lag_minutes <= 1560) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 1560 THEN '🔴 Data is out of date!' END AS status, '6h' AS update_period FROM terra.core.fact_blocks INNER JOIN terra.core.fact_transactions USING(BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 7 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), thorchain_info AS ( SELECT 'Thorchain' AS blockchain, BLOCK_ID AS 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 <= 720 THEN '🟢 Data is up to date!' WHEN (lag_minutes > 720 AND lag_minutes <= 840) THEN '🟡 Slightly out of date!' WHEN lag_minutes > 840 THEN '🔴 Data is out of date!' END AS status, '12h' AS update_period FROM thorchain.core.dim_block INNER JOIN thorchain.core.fact_transfers USING(DIM_BLOCK_ID, BLOCK_TIMESTAMP) WHERE BLOCK_TIMESTAMP > CURRENT_DATE() - 3 ORDER BY BLOCK_TIMESTAMP DESC NULLS LAST LIMIT 1 ), block_info AS ( SELECT * FROM arbitrum_info UNION SELECT * FROM avalanche_info UNION SELECT * FROM base_info UNION SELECT * FROM bitcoin_info UNION SELECT * FROM bsc_info UNION SELECT * FROM ethereum_info UNION SELECT * FROM gnosis_info UNION SELECT * FROM optimism_info UNION SELECT * FROM polygon_info UNION SELECT * FROM aurora_info UNION SELECT * FROM axelar_info UNION SELECT * FROM cosmos_info UNION SELECT * FROM flow_info UNION SELECT * FROM near_info UNION SELECT * FROM osmosis_info UNION SELECT * FROM sei_info UNION SELECT * FROM solana_info UNION SELECT * FROM terra_info UNION SELECT * FROM thorchain_info ) SELECT blockchain AS "Blockchain", lag AS "Data Lag", status AS "Data Status", update_period AS "Update Period", CURRENT_TIMESTAMP(0) AS "Last Dashboard Refresh", BLOCK_TIMESTAMP AS "Last Block Timestamp Available", BLOCK_NUMBER AS "Last Block Number" FROM block_info ORDER BY "Blockchain" ASC

_fallenleaf-eK3jnq