WITH futures_market_v1 AS ( SELECT '0xb147c69bee211f57290a6cde9d1babfd0dcf3ea3' AS address --FuturesMarket (v1 addresses) UNION ALL SELECT '0x10305c1854d6db8a1060df60bdf8a8b2981249cf' UNION ALL SELECT '0x9f231dbe53d460f359b2b8cc47574493caa5b7bf' UNION ALL SELECT '0xad44873632840144ffc97b2d1de716f6e2cf0366' UNION ALL SELECT '0xee8804d8ad10b0c3ad1bd57ac3737242ad24bb95' UNION ALL SELECT '0x4ff54624d5fb61c34c634c3314ed3bfe4dbb665a' UNION ALL SELECT '0xfe00395ec846240dc693e92ab2dd720f94765aa3' UNION ALL SELECT '0xbcb2d435045e16b059b2130b28be70b5ca47bfe5' UNION ALL SELECT '0x4434f56ddbde28fab08c4ae71970a06b300f8881' UNION ALL SELECT '0xf86048dff23cf130107dfb4e6386f574231a5c65' UNION ALL SELECT '0xcf853f7f8f78b2b801095b66f8ba9c5f04db1640' UNION ALL SELECT '0x3ed04ceff4c91872f19b1da35740c0be9ca21558' UNION ALL SELECT '0x9f1c2f0071bc3b31447aeda9fa3a68d651eb4632' UNION ALL SELECT '0x1228c7d8bbc5bc53db181bd7b1fce765aa83bf8a' UNION ALL SELECT '0x5af0072617f7f2aeb0e314e2fad1de0231ba97cd' UNION ALL SELECT '0x4aa0dabd22bc0894975324bec293443c8538bd08' UNION ALL SELECT '0x001b7876f567f0b3a639332ed1e363839c6d85e2' UNION ALL SELECT '0xd325b17d5c9c3f2b6853a760afcf81945b0184d3' ) , proxyperps_v2 AS ( SELECT '0xdcb8438c979fa030581314e5a5df42bbfed744a0' AS address --proxyperps_v2 UNION ALL SELECT '0x98ccbc721cc05e28a125943d69039b39be6a21e9' UNION ALL SELECT '0xd91db82733987513286b81e7115091d96730b62a' UNION ALL SELECT '0x031a448f59111000b96f016c37e9c71e57845096' UNION ALL SELECT '0x0ea09d97b4084d859328ec4bf8ebcf9ecca26f1d' UNION ALL SELECT '0x3d3f34416f60f77a0a6cc8e32abe45d32a7497cb' UNION ALL SELECT '0xd5fbf7136b86021ef9d0be5d798f948dce9c0dea' UNION ALL SELECT '0x4308427c463caeaab50fff98a9dec569c31e4e87' UNION ALL SELECT '0x139f94e4f0e1101c1464a321cba815c34d58b5d9' UNION ALL SELECT '0xb25529266d9677e9171beaf333a0dea506c5f99a' UNION ALL SELECT '0xc203a12f298ce73e44f7d45a4f59a43dbffe204d' UNION ALL SELECT '0x1dad8808d8ac58a0df912adc4b215ca3b93d6c49' UNION ALL SELECT '0x5b6beb79e959aac2659bee60fe0d0885468bf886' UNION ALL SELECT '0x2ea06e73083f1b3314fa090eae4a5f70eb058f2e' UNION ALL SELECT '0x27665271210acff4fab08ad9bb657e91866471f0' UNION ALL SELECT '0x87ae62c5720dab812bdacba66cc24839440048d1' UNION ALL SELECT '0x572f816f21f56d47e4c4fa577837bd3f58088676' UNION ALL SELECT '0x3a52b21816168dfe35be99b7c5fc209f17a0adb1' UNION ALL SELECT '0x9de146b5663b82f44e5052dede2aa3fd4cbcdc99' UNION ALL SELECT '0xf7d9bd13f877171f6c7f93f71bdf8e380335dc12' UNION ALL SELECT '0x442b69937a0daf9d46439a71567fabe6cb69fbaf' UNION ALL SELECT '0x074b8f19fc91d6b2eb51143e1f186ca0ddb88042' UNION ALL SELECT '0xbb16c7b3244dfa1a6bf83fcce3ee4560837763cd' UNION ALL SELECT '0xa1ace9ce6862e865937939005b1a6c5ac938a11f' UNION ALL SELECT '0x0940b0a96c5e1ba33aee331a9f950bb2a6f2fb25' UNION ALL SELECT '0xc18f85a6dd3bcd0516a1ca08d3b1f0a4e191a2c4' UNION ALL SELECT '0xc8fcd6fb4d15dd7c455373297def375a08942ece' UNION ALL SELECT '0x852210f0616ac226a486ad3387dbf990e690116a' UNION ALL SELECT '0x96690aae7cb7c4a9b5be5695e94d72827decc33f' UNION ALL SELECT '0xaa94c874b91ef16c8b56a1c5b2f34e39366bd484' UNION ALL SELECT '0x6110df298b411a46d6edce72f5caca9ad826c1de' UNION ALL SELECT '0xd5fccd43205cef11fbaf9b38df15adbe1b186869' UNION ALL SELECT '0x549dbdffbd47bd5639f9348ebe82e63e2f9f777a' UNION ALL SELECT '0x4bf3c1af0faa689e3a808e6ad7a8d89d07bb9ec7' UNION ALL SELECT '0x9615b6bfff240c44d3e33d0cd9a11f563a2e8d8b' UNION ALL SELECT '0x5374761526175b59f1e583246e20639909e189ce' UNION ALL SELECT '0x5ed8d0946b59d015f5a60039922b870537d43689' UNION ALL SELECT '0xb7059ed9950f2d9fdc0155fc0d79e63d4441e806' UNION ALL SELECT '0x8b9b5f94aac2316f048025b3cbe442386e85984b' UNION ALL SELECT '0x33d4613639603c845e61a02cd3d2a78be7d513dc' UNION ALL SELECT '0x2c5e2148bf3409659967fe3684fd999a76171235' UNION ALL SELECT '0x31a1659ca00f617e86dc765b6494afe70a5a9c1a' UNION ALL SELECT '0x509072a5ae4a87ac89fc8d64d94adcb44bd4b88e' UNION ALL SELECT '0x59b007e9ea8f89b069c43f8f45834d30853e3699' UNION ALL SELECT '0x2b3bb4c683bfc5239b029131eef3b1d214478d93' UNION ALL SELECT '0x6940e7c6125a177b052c662189bb27692e88e9cb' UNION ALL SELECT '0xf9dd29d2fd9b38cd90e390c797f1b7e0523f43a9' UNION ALL SELECT '0x69f5f465a46f324fb7bf3fd7c0d5c00f7165c7ea' UNION ALL SELECT '0x09f9d7aaa6bef9598c3b676c0e19c9786aa566a8' ) , tx as ( SELECT block_timestamp, tx_hash, CONCAT('0x', SUBSTR(topics[2], 27, 40)) AS aa --account FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address from futures_market_v1) AND topics[0] = '0x930fd93131df035ac630ef616ad4212af6370377bf327e905c2724cd01d95097' --PositionModified AND block_timestamp >= '2022-03-18' AND block_timestamp < current_date UNION ALL SELECT block_timestamp, tx_hash, CONCAT('0x', SUBSTR(topics[2], 27, 40)) AS aa --account FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address from proxyperps_v2) AND topics[0] = '0xc0d933baa356386a245ade48f9a9c59db4612af2b5b9c17de5b451c628760f43' --PositionModified AND block_timestamp >= '2022-03-18' AND block_timestamp < current_date ) , txaa AS ( SELECT date_trunc('day', block_timestamp) as date, APPROX_COUNT_DISTINCT(tx_hash) as dtra, APPROX_COUNT_DISTINCT(aa) as aa FROM tx WHERE block_timestamp >= current_date - 7 GROUP BY 1 ) , na AS ( SELECT date, APPROX_COUNT_DISTINCT(aa) as na FROM ( SELECT min(date_trunc('day', block_timestamp)) AS date, aa FROM tx WHERE block_timestamp >= current_date - 730 GROUP BY aa ) WHERE date >= current_date - 7 GROUP BY 1 ) , 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) ) , ma AS ( SELECT date_trunc('day', end_date) AS date, APPROX_COUNT_DISTINCT(aa) as ma FROM ( SELECT block_timestamp, aa FROM tx WHERE block_timestamp >= current_date - 37 ) JOIN ma_dates ON block_timestamp BETWEEN start_date AND end_date GROUP BY 1 ) , prices AS ( SELECT date_trunc('day', hour) AS date , 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 ) , dg AS ( SELECT date_trunc('day', block_timestamp) AS date, SUM(tx_fee * price) AS dg FROM ( SELECT block_timestamp, tx_fee FROM optimism.core.fact_transactions WHERE to_address IN (SELECT address from futures_market_v1 UNION ALL SELECT address from proxyperps_v2) 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) GROUP BY 1 ) , perps_tracking AS ( SELECT substr(topics[1], 3, 64) as trackingCode , substr(data, 3 + 64*1, 64) AS marketKey , substr(data, 3 + 64*0, 64) AS baseAsset , tx_hash , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT AS fee , contract_address FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address FROM proxyperps_v2) AND topics[0] = '0xc9d5ada2ea384fe04826ecd1b258955ac73c3e2e20d755108eafde90bc5588d4' --PerpsTracking AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , position_liquidated_v2 AS ( SELECT contract_address , tx_hash , block_timestamp , substr(data, 27 + 64*1, 40) as account , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT AS fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*4, 64))::FLOAT AS price , utils.udf_hex_to_int('s2c', substr(data, 3 + 64*3, 64))::FLOAT AS decoded_size FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address FROM proxyperps_v2) AND topics[0] = '0x62e7eb6698aabc6740afc94f06bbdfb947fc109fd24d4adb26014d44053ac2c3' --PositionLiquidated AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , position_modified_v2 AS ( SELECT contract_address , tx_hash , block_timestamp , substr(topics[2], 27, 40) as account , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT AS fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT AS lastPrice , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*0, 64))::FLOAT AS margin , utils.udf_hex_to_int('s2c', substr(data, 3 + 64*1, 64))::FLOAT AS scale , utils.udf_hex_to_int('s2c', substr(data, 3 + 64*2, 64))::FLOAT AS tradeSize FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address FROM proxyperps_v2) AND topics[0] IN ( '0x930fd93131df035ac630ef616ad4212af6370377bf327e905c2724cd01d95097' --PositionModified , '0xc0d933baa356386a245ade48f9a9c59db4612af2b5b9c17de5b451c628760f43' --PositionModified ) AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , position_liquidated_v1 AS ( SELECT contract_address , tx_hash , block_timestamp , substr(topics[2], 27, 40) as account , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*2, 64))::FLOAT AS fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*1, 64))::FLOAT AS price , utils.udf_hex_to_int('s2c', substr(data, 3 + 64*0, 64))::FLOAT AS decoded_size FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address FROM futures_market_v1) AND topics[0] = '0x62e7eb6698aabc6740afc94f06bbdfb947fc109fd24d4adb26014d44053ac2c3' --PositionLiquidated AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , futures_tracking AS ( SELECT substr(topics[1], 3, 64) as trackingCode , substr(data, 3 + 64*1, 64) AS marketKey , substr(data, 3 + 64*0, 64) AS baseAsset , tx_hash , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT AS fee , contract_address FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address FROM futures_market_v1) AND topics[0] = '0xc4be15f09b70f2353d054e324d6774570c38bcd0420aac862188ae7dcba2c6f6' --FuturesTracking AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , position_modified_v1 AS ( SELECT contract_address , tx_hash , block_timestamp , substr(topics[2], 27, 40) as account , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*5, 64))::FLOAT AS fee , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*3, 64))::FLOAT AS lastPrice , ethereum.public.udf_hex_to_int(substr(data, 3 + 64*0, 64))::FLOAT AS margin , utils.udf_hex_to_int('s2c', substr(data, 3 + 64*1, 64))::FLOAT AS scale , utils.udf_hex_to_int('s2c', substr(data, 3 + 64*2, 64))::FLOAT AS tradeSize FROM optimism.core.fact_event_logs WHERE contract_address IN (SELECT address FROM futures_market_v1) AND topics[0] = '0x930fd93131df035ac630ef616ad4212af6370377bf327e905c2724cd01d95097' --PositionModified AND block_timestamp >= current_date - 7 AND block_timestamp < current_date ) , liquidation_pre AS ( SELECT COALESCE(ft.trackingCode, NULL) as tracking_code , COALESCE(ft.marketKey, NULL) as marketKey , COALESCE(ft.baseAsset, NULL) as PERP , pm.contract_address, pm.tx_hash, pm.block_timestamp, pm.account, pm.fee/1e18 AS fee, pm.price/1e18 AS lastPrice , NULL AS margin, 0 AS scale, pm.decoded_size/1e18 * -1 AS tradeSize , CASE WHEN pm.decoded_size = 0 THEN 1 ELSE 0 END AS helper FROM position_liquidated_v2 pm LEFT JOIN perps_tracking ft ON pm.tx_hash = ft.tx_hash ) , perps_table AS ( SELECT COALESCE(marketKey, NULL) AS marketKey , COALESCE(baseAsset, NULL) as PERP , contract_address FROM perps_tracking GROUP BY 1,2,3 ) , liquidation_table AS ( SELECT NULL as tracking_code, t2.marketKey, t2.PERP, t1.contract_address , t1.tx_hash, t1.block_timestamp, t1.account, t1.fee, t1.lastPrice , t1.margin, t1.scale, t1.tradeSize, ABS((t1.tradeSize) * (t1.lastPrice)) AS volume , t1.helper FROM liquidation_pre t1 LEFT JOIN perps_table t2 ON t1.contract_address = t2.contract_address ) , perps_tx AS ( SELECT COALESCE(ft.trackingCode, NULL) as tracking_code , COALESCE(ft.marketKey, NULL) as marketKey , COALESCE(ft.baseAsset, NULL) as PERP , pm.contract_address, pm.tx_hash, pm.block_timestamp, pm.account, pm.fee/1e18 AS fee, pm.lastPrice/1e18 AS lastPrice , pm.margin/1e18 AS margin, pm.scale/1e18 AS scale, pm.tradeSize/1e18 AS tradeSize , ABS((pm.tradeSize / 1e18) * (pm.lastPrice / 1e18)) AS volume , CASE WHEN pm.scale = 0 THEN 1 ELSE 0 END AS helper FROM position_modified_v2 pm LEFT JOIN perps_tracking ft ON pm.tx_hash = ft.tx_hash WHERE ft.tx_hash IS NOT NULL ) , trades AS ( SELECT date_trunc('day', block_timestamp) AS day, * FROM perps_tx UNION DISTINCT SELECT date_trunc('day', block_timestamp) AS day, * FROM liquidation_table ) , Perp_V2_vol AS ( SELECT day, SUM(volume) AS PERPv2_vol FROM trades GROUP BY day ) , liquidation_pre_v1 AS ( SELECT COALESCE(ft.trackingCode, NULL) as tracking_code , COALESCE(ft.marketKey, NULL) as marketKey , COALESCE(ft.baseAsset, NULL) as PERP , pm.contract_address, pm.tx_hash, pm.block_timestamp, pm.account, pm.fee/1e18 AS fee, pm.price/1e18 AS lastPrice , NULL AS margin, 0 AS scale, pm.decoded_size/1e18 * -1 AS tradeSize , CASE WHEN pm.decoded_size = 0 THEN 1 ELSE 0 END AS helper FROM position_liquidated_v1 pm LEFT JOIN futures_tracking ft ON pm.tx_hash = ft.tx_hash ) , perps_table_v1 AS ( SELECT COALESCE(marketKey, NULL) AS marketKey , COALESCE(baseAsset, NULL) as PERP , contract_address FROM futures_tracking GROUP BY 1,2,3 ) , liquidation_table_v1 AS ( SELECT NULL as tracking_code, t2.marketKey, t2.PERP, t1.contract_address , t1.tx_hash, t1.block_timestamp, t1.account, t1.fee, t1.lastPrice , t1.margin, t1.scale, t1.tradeSize, ABS((t1.tradeSize) * (t1.lastPrice)) AS volume , t1.helper FROM liquidation_pre_v1 t1 LEFT JOIN perps_table_v1 t2 ON t1.contract_address = t2.contract_address ) , perps_tx_v1 AS ( SELECT COALESCE(ft.trackingCode, NULL) as tracking_code , COALESCE(ft.marketKey, NULL) as marketKey , COALESCE(ft.baseAsset, NULL) as PERP , pm.contract_address, pm.tx_hash, pm.block_timestamp, pm.account, pm.fee/1e18 AS fee, pm.lastPrice/1e18 AS lastPrice , pm.margin/1e18 AS margin, pm.scale/1e18 AS scale, pm.tradeSize/1e18 AS tradeSize , ABS((pm.tradeSize / 1e18) * (pm.lastPrice / 1e18)) AS volume , CASE WHEN pm.scale = 0 THEN 1 ELSE 0 END AS helper FROM position_modified_v1 pm LEFT JOIN futures_tracking ft ON pm.tx_hash = ft.tx_hash WHERE ft.tx_hash IS NOT NULL ) , trades_v1 AS ( SELECT date_trunc('day', block_timestamp) AS day, * FROM perps_tx_v1 UNION DISTINCT SELECT date_trunc('day', block_timestamp) AS day, * FROM liquidation_table_v1 ) , Perp_V1_vol as ( select day, SUM(volume) as PERPv1_vol from trades_v1 GROUP BY day ) , swap_vol AS ( select date_trunc('day', block_timestamp) AS date , SUM(amount_out_usd) AS swap_vol FROM optimism.defi.ez_dex_swaps WHERE platform = 'synthetix' AND block_timestamp < current_date AND block_timestamp >= current_date - 7 GROUP BY 1 ) , 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 , 'optimism' AS blockchain , COALESCE(dtra, 0) AS dtra , COALESCE(aa, 0) AS aa , COALESCE(na, 0) AS na , COALESCE(ma, 0) AS ma , COALESCE(dg, 0) AS dg , coalesce(f.PERPv1_vol, 0) + coalesce(g.PERPv2_vol, 0) + coalesce(h.swap_vol, 0) as dtveu FROM dates a LEFT JOIN txaa b ON b.date = a.date LEFT JOIN na c ON c.date = a.date LEFT JOIN ma d ON d.date = a.date LEFT JOIN dg e ON e.date = a.date LEFT JOIN Perp_V1_vol f ON f.day = a.date LEFT JOIN Perp_V2_vol g ON g.day = a.date LEFT JOIN swap_vol h ON h.date = a.date ORDER BY 1 DESC

Query	Creator	Created
093ae uniswap kyberswap woofi hashflow dodo	ouznezgazkeklnz39187	Aug 3, 2023
476ae uniswap kyberswap woofi hashflow quickswap	ouznezgazkeklnz39187	Aug 8, 2023

ouznezgazkeklnz39187