WITH tx AS ( SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'arbitrum' as blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xa19fd5ab6c8dcffa2a295f78a5bb4ac543aaf5e3' -- Orderbook AND topics[0] = '0x66340b656a5a83a68f9bf9206f8b20299c11d9c3c7c5ae3bc6ab4ba12f82927e' --NewLiquidityOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'arbitrum' as blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xa19fd5ab6c8dcffa2a295f78a5bb4ac543aaf5e3' -- Orderbook AND topics[0] = '0x00da46badc3d23e4ffbd09bb00128a2274573502fdbb15d233ec3441c01b6af3' --NewPositionOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'arbitrum' as blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xa19fd5ab6c8dcffa2a295f78a5bb4ac543aaf5e3' -- Orderbook AND topics[0] = '0x201d03547a8fd1f2eaa0fda90af89681a6c4dc6a8e495565e5ca6a62b3579cf0' --NewWithdrawalOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'arbitrum' as blockchain FROM arbitrum.core.fact_event_logs WHERE contract_address = '0xa19fd5ab6c8dcffa2a295f78a5bb4ac543aaf5e3' -- Orderbook AND topics[0] = '0x5c1a7ceefa482bcc616dec79c7a8bb748520ec46eb6d550f10ecf97e70f1c96a' --NewRebalanceOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'avalanche' as blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add' -- Orderbook AND topics[0] = '0x66340b656a5a83a68f9bf9206f8b20299c11d9c3c7c5ae3bc6ab4ba12f82927e' --NewLiquidityOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'avalanche' as blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add' -- Orderbook AND topics[0] = '0x00da46badc3d23e4ffbd09bb00128a2274573502fdbb15d233ec3441c01b6af3' --NewPositionOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'avalanche' as blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add' -- Orderbook AND topics[0] = '0x201d03547a8fd1f2eaa0fda90af89681a6c4dc6a8e495565e5ca6a62b3579cf0' --NewWithdrawalOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'avalanche' as blockchain FROM avalanche.core.fact_event_logs WHERE contract_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add' -- Orderbook AND topics[0] = '0x5c1a7ceefa482bcc616dec79c7a8bb748520ec46eb6d550f10ecf97e70f1c96a' --NewRebalanceOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'bnb' as blockchain FROM bsc.core.fact_event_logs WHERE contract_address = '0xa67aa293642c4e02d1b9f360b007c0dbdc451a08' -- Orderbook AND topics[0] = '0x66340b656a5a83a68f9bf9206f8b20299c11d9c3c7c5ae3bc6ab4ba12f82927e' --NewLiquidityOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'bnb' as blockchain FROM bsc.core.fact_event_logs WHERE contract_address = '0xa67aa293642c4e02d1b9f360b007c0dbdc451a08' -- Orderbook AND topics[0] = '0x00da46badc3d23e4ffbd09bb00128a2274573502fdbb15d233ec3441c01b6af3' --NewPositionOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'bnb' as blockchain FROM bsc.core.fact_event_logs WHERE contract_address = '0xa67aa293642c4e02d1b9f360b007c0dbdc451a08' -- Orderbook AND topics[0] = '0x201d03547a8fd1f2eaa0fda90af89681a6c4dc6a8e495565e5ca6a62b3579cf0' --NewWithdrawalOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'bnb' as blockchain FROM bsc.core.fact_event_logs WHERE contract_address = '0xa67aa293642c4e02d1b9f360b007c0dbdc451a08' -- Orderbook AND topics[0] = '0x5c1a7ceefa482bcc616dec79c7a8bb748520ec46eb6d550f10ecf97e70f1c96a' --NewRebalanceOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL -- SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'fantom' as blockchain -- FROM fantom.core.fact_event_logs -- WHERE contract_address = '0x0c30b10462cded51c3ca31e7c51019b7d25a965b' -- Orderbook -- AND topics[0] = '0x66340b656a5a83a68f9bf9206f8b20299c11d9c3c7c5ae3bc6ab4ba12f82927e' --NewLiquidityOrder -- AND block_timestamp >= '2022-05-07' -- AND block_timestamp < current_date -- UNION ALL -- SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'fantom' as blockchain -- FROM fantom.core.fact_event_logs -- WHERE contract_address = '0x0c30b10462cded51c3ca31e7c51019b7d25a965b' -- Orderbook -- AND topics[0] = '0x00da46badc3d23e4ffbd09bb00128a2274573502fdbb15d233ec3441c01b6af3' --NewPositionOrder -- AND block_timestamp >= '2022-05-07' -- AND block_timestamp < current_date -- UNION ALL -- SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'fantom' as blockchain -- FROM fantom.core.fact_event_logs -- WHERE contract_address = '0x0c30b10462cded51c3ca31e7c51019b7d25a965b' -- Orderbook -- AND topics[0] = '0x201d03547a8fd1f2eaa0fda90af89681a6c4dc6a8e495565e5ca6a62b3579cf0' --NewWithdrawalOrder -- AND block_timestamp >= '2022-05-07' -- AND block_timestamp < current_date -- UNION ALL -- SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'fantom' as blockchain -- FROM fantom.core.fact_event_logs -- WHERE contract_address = '0x0c30b10462cded51c3ca31e7c51019b7d25a965b' -- Orderbook -- AND topics[0] = '0x201d03547a8fd1f2eaa0fda90af89681a6c4dc6a8e495565e5ca6a62b3579cf0' --NewRebalanceOrder -- AND block_timestamp >= '2022-05-07' -- AND block_timestamp < current_date -- UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'optimism' as blockchain FROM optimism.core.fact_event_logs WHERE contract_address = '0x6fde9892fd5302ac3c68688085bd5b031a63bc9d' -- Orderbook AND topics[0] = '0x66340b656a5a83a68f9bf9206f8b20299c11d9c3c7c5ae3bc6ab4ba12f82927e' --NewLiquidityOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'optimism' as blockchain FROM optimism.core.fact_event_logs WHERE contract_address = '0x6fde9892fd5302ac3c68688085bd5b031a63bc9d' -- Orderbook AND topics[0] = '0x00da46badc3d23e4ffbd09bb00128a2274573502fdbb15d233ec3441c01b6af3' --NewPositionOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 3, 40) as aa, 'optimism' as blockchain FROM optimism.core.fact_event_logs WHERE contract_address = '0x6fde9892fd5302ac3c68688085bd5b031a63bc9d' -- Orderbook AND topics[0] = '0x201d03547a8fd1f2eaa0fda90af89681a6c4dc6a8e495565e5ca6a62b3579cf0' --NewWithdrawalOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, SUBSTRING(topics[1], 27, 40) as aa, 'optimism' as blockchain FROM optimism.core.fact_event_logs WHERE contract_address = '0x6fde9892fd5302ac3c68688085bd5b031a63bc9d' -- Orderbook AND topics[0] = '0x5c1a7ceefa482bcc616dec79c7a8bb748520ec46eb6d550f10ecf97e70f1c96a' --NewRebalanceOrder AND block_timestamp >= '2022-05-07' AND block_timestamp < current_date ) , blockchains AS ( SELECT 'arbitrum' AS blockchain UNION ALL SELECT 'avalanche' AS blockchain UNION ALL SELECT 'optimism' AS blockchain UNION ALL SELECT 'bnb' AS blockchain ) , txaab AS ( SELECT date_trunc('day', block_timestamp) as date, blockchain, APPROX_COUNT_DISTINCT(tx_hash) as tx, APPROX_COUNT_DISTINCT(aa) as aa FROM tx WHERE block_timestamp >= current_date - 7 GROUP BY 1,2 ) , nab AS ( SELECT date, blockchain, APPROX_COUNT_DISTINCT(aa) as na FROM ( SELECT min(date_trunc('day', block_timestamp)) AS date, blockchain, aa FROM tx WHERE block_timestamp >= current_date - 730 GROUP BY blockchain, aa ) WHERE date >= current_date - 7 GROUP BY 1,2 ) , ma_dates AS ( SELECT DATEADD('second', 86399, current_date - 7)::TIMESTAMP as end_date, DATEADD('day', -30, end_date)::TIMESTAMP as start_date UNION ALL SELECT DATEADD(day, 1, end_date) as end_date, DATEADD('day', 1, start_date) as start_date FROM ma_dates WHERE DATEADD('day', 1, end_date) <= DATEADD('second', 86399, current_date - 1) ) , mab AS ( SELECT date_trunc('day', end_date) AS date, blockchain, APPROX_COUNT_DISTINCT(aa) as ma FROM ( SELECT block_timestamp, blockchain, aa FROM tx WHERE block_timestamp >= current_date - 37 ) JOIN ma_dates ON block_timestamp BETWEEN start_date AND end_date GROUP BY 1,2 ) , prices AS ( SELECT date_trunc('day', hour) AS date , 'ETH' AS chain , AVG(price) AS price FROM ethereum.price.ez_prices_hourly WHERE token_address = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') -- WETH AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2 UNION ALL SELECT date_trunc('day', hour) AS date , 'BNB' AS chain , AVG(price) AS price FROM bsc.price.ez_prices_hourly WHERE token_address = '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c' -- WBNB AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2 UNION ALL SELECT date_trunc('day', hour) AS date , 'AVAX' AS chain , AVG(price) AS price FROM avalanche.price.ez_prices_hourly WHERE token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' -- WAVAX AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2 ) , dgb AS ( SELECT date_trunc('day', block_timestamp) AS date, blockchain, SUM(tx_fee * price) AS dg FROM ( SELECT block_timestamp, 'arbitrum' AS blockchain, 'ETH' AS chain, tx_fee FROM arbitrum.core.fact_transactions WHERE to_address = '0xa19fd5ab6c8dcffa2a295f78a5bb4ac543aaf5e3' --Orderbook AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'avalanche' AS blockchain, 'AVAX' AS chain, tx_fee FROM avalanche.core.fact_transactions WHERE to_address = '0x5898c3e218a8501533d771c86e2fa37743ea2add' --Orderbook AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'bnb' AS blockchain, 'BNB' AS chain, tx_fee FROM bsc.core.fact_transactions WHERE to_address = '0xa67aa293642c4e02d1b9f360b007c0dbdc451a08' --Orderbook AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp, 'optimism' AS blockchain, 'ETH' AS chain, tx_fee FROM optimism.core.fact_transactions WHERE to_address = '0x6fde9892fd5302ac3c68688085bd5b031a63bc9d' --Orderbook AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) t LEFT JOIN prices p ON p.date = date_trunc('day', t.block_timestamp) AND p.chain = t.chain GROUP BY 1,2 ) , dates AS ( SELECT (current_date - 7)::TIMESTAMP AS date UNION ALL SELECT DATEADD(DAY, 1, date)::TIMESTAMP FROM dates WHERE date < current_date - 1 ) SELECT a.date AS date , b.blockchain AS blockchain , COALESCE(tx, 0) AS dtra , COALESCE(aa, 0) AS aa , COALESCE(na, 0) AS na , COALESCE(ma, 0) AS ma , COALESCE(dg, 0) AS dg FROM dates a CROSS JOIN blockchains b LEFT JOIN txaab c ON c.date = a.date AND c.blockchain = b.blockchain LEFT JOIN nab d ON d.date = a.date AND d.blockchain = b.blockchain LEFT JOIN mab f ON f.date = a.date AND f.blockchain = b.blockchain LEFT JOIN dgb g ON g.date = a.date AND g.blockchain = b.blockchain ORDER BY 1 DESC

Query	Creator	Created
532o bsc arbitrum ethereum optimism avalanche	ouznezgazkeklnz39187	Aug 2, 2023
533o bsc arbitrum ethereum l2 evm	ouznezgazkeklnz39187	Aug 2, 2023
All Chains Latency - Top-level Categories copy terra cosmos gnosis flow axelar	ouznezgazkeklnz39187	May 10, 2023
003e bsc evm binance	ouznezgazkeklnz39187	May 9, 2023
535o bsc base ethereum l2 evm	ouznezgazkeklnz39187	Aug 4, 2023

ouznezgazkeklnz39187