-- forked from F oapebna @ https://flipsidecrypto.xyz/edit/queries/23871362-e0be-4567-bc90-d9f255c290a3 WITH Flow as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT tx_id) AS tx_count , (SELECT COUNT(DISTINCT tx_id)/1440 FROM flow.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01' and TX_SUCCEEDED = true) AS jan_tpm , (SELECT COUNT(DISTINCT tx_id)/1440 FROM flow.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01' and TX_SUCCEEDED = true) AS jul_tpm , COUNT(DISTINCT BLOCK_HEIGHT) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from flow.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and TX_SUCCEEDED = true group by 1 order by 1 ), osmo as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT tx_id) AS tx_count , (SELECT COUNT(DISTINCT tx_id)/1440 FROM osmosis.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01' and TX_SUCCEEDED = 'true' ) AS jan_tpm , (SELECT COUNT(DISTINCT tx_id)/1440 FROM osmosis.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01' and TX_SUCCEEDED = 'true' ) AS jul_tpm , COUNT(DISTINCT BLOCK_ID) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from osmosis.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and TX_SUCCEEDED = 'true' group by 1 order by 1 ), Optimism as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT tx_hash) AS tx_count , (SELECT COUNT(DISTINCT tx_hash)/1440 FROM optimism.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01' ) AS jan_tpm , (SELECT COUNT(DISTINCT tx_hash)/1440 FROM optimism.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01' ) AS jul_tpm , COUNT(DISTINCT BLOCK_NUMBER) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from optimism.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and status = 'SUCCESS' group by 1 order by 1 ), polygon as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT TX_HASH) AS tx_count , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM polygon.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01'and STATUS = 'SUCCESS' ) AS jan_tpm , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM polygon.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01'and STATUS = 'SUCCESS' ) AS Jul_tpm , COUNT(DISTINCT BLOCK_NUMBER) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from polygon.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and STATUS = 'SUCCESS' group by 1 order by 1 ), Arbitrum as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT TX_HASH) AS tx_count , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM arbitrum.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01'and STATUS = 'SUCCESS' ) AS jan_tpm , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM arbitrum.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01'and STATUS = 'SUCCESS' ) AS jul_tpm , COUNT(DISTINCT BLOCK_NUMBER) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from Arbitrum.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and STATUS = 'SUCCESS' group by 1 order by 1 ), bsc as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT TX_HASH) AS tx_count , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM bsc.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01'and STATUS = 'SUCCESS' ) AS jan_tpm , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM bsc.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01'and STATUS = 'SUCCESS' ) AS jul_tpm , COUNT(DISTINCT BLOCK_NUMBER) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from bsc.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and STATUS = 'SUCCESS' group by 1 order by 1 ), near as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT TX_HASH) AS tx_count , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM near.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01'and TX_STATUS = 'Success' ) AS jan_tpm , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM near.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01'and TX_STATUS = 'Success' ) AS jul_tpm , COUNT(DISTINCT BLOCK_ID) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from near.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and TX_STATUS = 'Success' group by 1 order by 1 ), avalanche as ( SELECT date_trunc('month',block_timestamp::date) AS date , COUNT(DISTINCT TX_HASH) AS tx_count , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM avalanche.core.fact_transactions WHERE block_timestamp >= '2023-01-01' AND block_timestamp < '2023-02-01'and STATUS = 'SUCCESS' ) AS jan_tpm , (SELECT COUNT(DISTINCT TX_HASH)/1440 FROM avalanche.core.fact_transactions WHERE block_timestamp >= '2023-07-01' AND block_timestamp < '2023-08-01'and STATUS = 'SUCCESS' ) AS Jul_tpm , COUNT(DISTINCT BLOCK_NUMBER) AS block_count, tx_count / block_count AS tx_per_block, (block_count/24) block_per_hour, (tx_count/86400) AS TPS, (tx_count/1440) as TPM, (tx_count/24) AS TPH , Lag(TPM) OVER (ORDER BY date) AS lag , TPM - lag AS change, change*100 / TPM AS change_perc from avalanche.core.fact_transactions where block_timestamp >= '2023-01-01' AND block_timestamp < '2023-08-01' and STATUS = 'SUCCESS' group by 1 order by 1 ) SELECT 'Flow' AS blockchain , * FROM Flow UNION ALL SELECT 'Osmosis' AS blockchain , * FROM osmo UNION ALL SELECT 'Optimism' AS blockchain , * FROM Optimism UNION ALL SELECT 'Polygon' AS blockchain , * FROM polygon UNION ALL SELECT 'Arbitrum' AS blockchain , * FROM Arbitrum UNION ALL SELECT 'Binance Smart Chain' AS blockchain , * FROM bsc UNION ALL SELECT 'Near' AS blockchain , * FROM near UNION ALL SELECT 'Avalanche' AS blockchain , * FROM avalanche

Avalanche Speed Comparison