Query editor
-- This query calculates the total volume and the average "Gained Premium" for ALL Reserve Auction markets.-- This definitive version is CORRECTED to fix syntax errors.-- DATE RANGE: February 1, 2025, and onward.WITH-- Step 1: Get hourly prices for WELL on both chains.base_well_price AS (SELECT price, DATE_TRUNC('hour', "timestamp") AS timestampFROM prices.hour WHERE blockchain = 'base' AND contract_address = 0xA88594D404727625A9437C3f886C7643872296AE),optimism_well_price AS (SELECT price, DATE_TRUNC('hour', "timestamp") AS timestampFROM prices.hour WHERE blockchain = 'optimism' AND contract_address = 0xA88594D404727625A9437C3f886C7643872296AE),-- Step 2: Create a COMPLETE map of all auction markets to their reserve tokens.market_to_reserve_mapping AS (SELECT * FROM (VALUES-- Base Markets(0x9f2ca3c6Cd1dddb7aD473a0a893C3104E2af15Ad, 0x833589fCD6eDb6E08f4c7C32D4f71b54bda02913, 18, 'DAI (Base)', 'base'),(0x8373155335839e3D078f3F224E8B6618Fc26eF17, 0x833589fCD6eDb6E08f4c7C32D4f71b54bda02913, 6, 'USDC (Base)', 'base'),(0x89b89c30E2f60Bd30059c3924eF5b8c0Fcd1B64A, 0x833589fCD6eDb6E08f4c7C32D4f71b54bda02913, 6, 'USDBC (Base)', 'base'),(0x064D8Cb3B7a22F4cFBdd602eBC7E722Bb71405D8, 0x4200000000000000000000000000000000000006, 18, 'WETH (Base)', 'base'),(0x48bc4876D33Db30929c373c3B949b66CB8d641F3, 0x2Ae3F1Ec7F1F5012CFEab0185bfc7aa3cf0DEc22, 18, 'cbETH (Base)', 'base'),(0xbd22DaFeF550094A32f388CD256FE133a0A14387, 0x7f39C581F595B53c5cb19bD0b3f8dA6c935E2Ca0, 18, 'wstETH (Base)', 'base'),(0xEfE30785362225106367039971d82715dcB35192, 0xae78736Cd615f374D3085123A210448E74Fc6393, 18, 'rETH (Base)', 'base'),(0xc7840e86A0aa22c23BCbC153CE61f6009733bf2C, 0x940181a94A35A4569E4529A3CDfB74e38FD98631, 18, 'AERO (Base)', 'base'),(0x75494780E76bB41c0fDf29DBA4b2Ce82501c12b0, 0xCd5fE23C85820F7B72D092454F4839595AFb4B81, 18, 'weETH (Base)', 'base'),(0x83D37e3df05F1507667AF4dfc83EC8A38Cf2dA08, 0x1b7d20ee553d2521115f211da1d5a58253144549, 8, 'cbBTC (Base)', 'base'),(0x7bBe5972e01BAc64fE3AD7EFfBa6D164f0a1F15f, 0xEB466342C4d449BC9f53A865D5Cb90586f405215, 6, 'EURC (Base)', 'base'),(0xe34D7D109B97e1b1DAc9A9920e6A6769814Ac7eE, 0xB9d3C099938e6ADDD837131B37D185f8525474d2, 18, 'wrsETH (Base)', 'base'),(0xA078017f827DC7B8540C98A3bF7b2153B2aF6cB3, 0x0000000000000000000000000000000000000000, 18, 'USDS (Base)', 'base'),(0x84C74431200Bcd3Ba4b557024734891857b43354, 0x6c3534E6B9532495520A4A85324761a0d041A412, 8, 'TBTC (Base)', 'base'),(0xf8f7b937a4CC6Cc16b600B3611ce0c1152a5b3F9, 0x2a73022946599A9293341806618A114384e45144, 8, 'LBTC (Base)', 'base'),-- Optimism Markets(0x475d7c6999dc27E640d260aBf9f2fA9333E472CF, 0x0b2C639c533813f4Aa9D7837CAf62653d097Ff85, 6, 'USDC (Optimism)', 'optimism'),(0x9E58891D8DF4e6Dd8bAfD3082A59B72C51202841, 0x94b008aA00579c1307B0EF2c499aD98a8ce58e58, 6, 'USDT (Optimism)', 'optimism'),(0xE6Aea947c0F082c5Dc751BB9C7f44Ce059590962, 0xDA10009cBd5D07dd0CeCc66161FC93D7c9000da1, 18, 'DAI (Optimism)', 'optimism'),(0x080D64570a58FF87E14CC5Cb91d1aaB26b15CFDc, 0x4200000000000000000000000000000000000006, 18, 'WETH (Optimism)', 'optimism'),(0x8455D94e412A498Df8727D904252892Fb111a4cD, 0x2Ae3F1Ec7F1F5012CFEab0185bfc7aa3cf0DEc22, 18, 'cbETH (Optimism)', 'optimism'),(0x01c369a6238226702E48C9C3fBB1de33F4b05D74, 0x1F32b1c2345538c0c6f582fCB022739c4A194Ebb, 18, 'wstETH (Optimism)', 'optimism'),(0x9E530e9F3f9b1046e223cc3eB97fA0bBab5Dd993, 0xae78736Cd615f374D3085123A210448E74Fc6393, 18, 'rETH (Optimism)', 'optimism'),(0x3B40085872eaEA59CF39FCafFb3dc36085aE48f6, 0xCd5fE23C85820F7B72D092454F4839595AFb4B81, 18, 'weETH (Optimism)', 'optimism'),(0xFfF466528fE1a18b95Fa910C96540A70EC2727FB, 0xB9d3C099938e6ADDD837131B37D185f8525474d2, 18, 'wrsETH (Optimism)', 'optimism'),(0x6427D36153dE11b694d70604B0715790769024f7, 0x4200000000000000000000000000000000000042, 18, 'OP (Optimism)', 'optimism'),(0x589F59fBDB5952920fA557c924F6f5CFf184b155, 0x4200000000000000000000000000000000000021, 18, 'VELO (Optimism)', 'optimism'),(0x78a9C06188195CEE3cBf67303a1708cb8765b9ec, 0x68f180fcCe6836688e9084f035309E29Bf0A2095, 8, 'WBTC (Optimism)', 'optimism')) AS t (market_address, reserve_address, reserve_decimals, market_name, blockchain)),-- Step 3: Get hourly prices for all reserve assets.base_reserve_prices AS (SELECT DATE_TRUNC('hour', minute) AS hour, contract_address, priceFROM prices.usd WHERE blockchain = 'base' AND contract_address IN (SELECT reserve_address FROM market_to_reserve_mapping WHERE blockchain = 'base')),optimism_reserve_prices AS (SELECT DATE_TRUNC('hour', minute) AS hour, contract_address, priceFROM prices.usd WHERE blockchain = 'optimism' AND contract_address IN (SELECT reserve_address FROM market_to_reserve_mapping WHERE blockchain = 'optimism')),