Query editor
-- This query calculates the effective discount of the reserve auctions.-- This definitive version uses the dex.trades table and includes a fix to prevent division-by-zero errors.WITH-- Step 1: Map auction contracts to their underlying reserve asset tokens and decimals.market_to_reserve_mapping AS (SELECT * FROM (VALUES-- Base Markets (Sample - add more as needed)(0x9f2ca3c6Cd1dddb7aD473a0a893C3104E2af15Ad, 0x833589fCD6eDb6E08f4c7C32D4f71b54bda02913, 18), -- DAI on Base(0x8373155335839e3D078f3F224E8B6618Fc26eF17, 0x833589fCD6eDb6E08f4c7C32D4f71b54bda02913, 6), -- USDBC on Base (using USDC price proxy, 6 decimals)(0x064D8Cb3B7a22F4cFBdd602eBC7E722Bb71405D8, 0x4200000000000000000000000000000000000006, 18) -- WETH on Base) AS t (market_address, reserve_address, reserve_decimals)),-- Step 2: Get hourly USD prices for all reserve assets.reserve_prices AS (SELECTDATE_TRUNC('hour', minute) AS hour,contract_address,priceFROM prices.usdWHERE blockchain = 'base' AND contract_address IN (SELECT reserve_address FROM market_to_reserve_mapping)),-- Step 3: Build a custom, reliable price feed for WELL in USD.-- 3a: Calculate the median price of WELL in terms of WETH.well_weth_hourly_price AS (SELECTDATE_TRUNC('hour', block_time) AS hour,APPROX_PERCENTILE(CASEWHEN token_bought_address = 0xA88594D404727625A9437C3f886C7643872296AE THEN token_sold_amount / token_bought_amountWHEN token_sold_address = 0xA88594D404727625A9437C3f886C7643872296AE THEN token_bought_amount / token_sold_amountEND,0.5) AS well_in_wethFROM dex.tradesWHEREblockchain = 'base'AND ((token_bought_address = 0xA88594D404727625A9437C3f886C7643872296AE AND token_sold_address = 0x4200000000000000000000000000000000000006) OR(token_bought_address = 0x4200000000000000000000000000000000000006 AND token_sold_address = 0xA88594D404727625A9437C3f886C7643872296AE))-- *** THE CRITICAL FIX IS HERE: Ensure we only use trades with valid, non-zero amounts. ***AND token_bought_amount > 0AND token_sold_amount > 0GROUP BY 1),-- 3b: Get the hourly price of WETH in USD.weth_hourly_price AS (SELECTDATE_TRUNC('hour', minute) AS hour,price AS weth_in_usdFROM prices.usdWHERE contract_address = 0x4200000000000000000000000000000000000006 -- WETH on BaseAND blockchain = 'base'),-- 3c: Combine them to get the final WELL price in USD.well_usd_prices AS (SELECT