WITH tx as ( SELECT block_timestamp, tx_hash, CONCAT('0x', SUBSTR(topics[1], 27, 40)) as aa, (ABS(utils.udf_hex_to_int('s2c', substr(data, 3 + 64*1, 64)))/1e18) AS amount --positionNotional , 'gnosis' AS blockchain FROM gnosis.core.fact_event_logs WHERE contract_address = '0x5d9593586b4b5edbd23e7eba8d88fd8f09d83ebd' -- ClearingHouse AND topics[0] = lower('0x4c7b764f428c13bbea8cc8da90ebe6eef4dafeb27a4e3d9041d64208c47ca7c2') -- PositionChanged AND block_timestamp >= '2020-12-14' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, decoded_log:trader AS aa, (ABS(decoded_log:exchangedPositionNotional) / 1e18) AS amount, 'optimism' AS blockchain FROM optimism.core.fact_decoded_event_logs WHERE contract_address IN ( '0x82ac2ce43e33683c58be4cdc40975e73aa50f459' -- ClearingHouse , '0x8098c6273bd5f9d32d03e6cb62472a9e6608eff2' -- ClearingHouse , '0x4f7961ee13bda96bfa9381b87d21b2baed96f0b5' -- ClearingHouse ) AND event_name = 'PositionChanged' AND block_timestamp >= '2021-11-24' AND block_timestamp < current_date ) , blockchains AS ( SELECT 'gnosis' AS blockchain UNION ALL SELECT 'optimism' 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 , 'GNO' AS chain , AVG(price) AS price FROM gnosis.price.ez_prices_hourly WHERE token_address = '0x9c58bacc331c9aa871afd802db6379a98e80cedb' -- gno AND hour >= current_date - 7 AND hour < current_date GROUP BY 1,2 UNION ALL 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 ) , dgb AS ( SELECT date_trunc('day', block_timestamp) AS date, blockchain, SUM(tx_fee * price) AS dg FROM ( SELECT block_timestamp, 'gnosis' AS blockchain, 'GNO' AS chain, tx_fee FROM gnosis.core.fact_transactions WHERE to_address = '0x5d9593586b4b5edbd23e7eba8d88fd8f09d83ebd' -- ClearingHouse 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 IN ( '0x82ac2ce43e33683c58be4cdc40975e73aa50f459' -- ClearingHouse , '0x8098c6273bd5f9d32d03e6cb62472a9e6608eff2' -- ClearingHouse , '0x4f7961ee13bda96bfa9381b87d21b2baed96f0b5' -- ClearingHouse ) 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 ) , dfu AS ( SELECT date_trunc('day', block_timestamp) AS date , blockchain , SUM(amount) AS dfu FROM ( SELECT block_timestamp , 'gnosis' AS blockchain , (ethereum.public.udf_hex_to_int(SUBSTR(data, 195, 64))::FLOAT / 1e18) AS amount -- data3 FROM gnosis.core.fact_event_logs WHERE contract_address = '0x5d9593586b4b5edbd23e7eba8d88fd8f09d83ebd' -- ClearingHouse AND topics[0] = lower('0x4c7b764f428c13bbea8cc8da90ebe6eef4dafeb27a4e3d9041d64208c47ca7c2') -- PositionChanged AND block_timestamp >= current_date - 7 AND block_timestamp < current_date UNION ALL SELECT block_timestamp , 'optimism' AS blockchain , (ethereum.public.udf_hex_to_int(SUBSTR(data, 131, 64))::FLOAT / 1e18) AS amount -- data2 FROM optimism.core.fact_event_logs WHERE contract_address IN ( '0x82ac2ce43e33683c58be4cdc40975e73aa50f459' -- ClearingHouse , '0x8098c6273bd5f9d32d03e6cb62472a9e6608eff2' -- ClearingHouse , '0x4f7961ee13bda96bfa9381b87d21b2baed96f0b5' -- ClearingHouse ) AND topics[0] = lower('0x968bc4f738eae0486dc6736c4b427dbafa4acfdf6eaf223337791ddeb3a56247') -- PositionChanged AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) GROUP BY 1,2 ) , tv AS ( SELECT date_trunc('day', block_timestamp) AS date , blockchain , SUM(amount) AS dtveu FROM tx WHERE block_timestamp >= current_date - 7 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 , COALESCE(dfu, 0) AS dfu , COALESCE(dtveu, 0) AS dtveu 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 LEFT JOIN dfu h ON h.date = a.date AND h.blockchain = b.blockchain LEFT JOIN tv i ON i.date = a.date AND i.blockchain = b.blockchain ORDER BY 1 DESC

Query	Creator	Created
084m gnosis evm	ouznezgazkeklnz39187	May 13, 2023
287t gnosis ethereum optimism l2 evm	ouznezgazkeklnz39187	May 23, 2023
All Chains Latency - Top-level Categories copy terra cosmos gnosis flow axelar	ouznezgazkeklnz39187	May 10, 2023

ouznezgazkeklnz39187