Query editor
-- This query calculates the average "Gained Premium" for each bi-weekly auction cycle-- to verify the historical data presented in the proposal draft.-- This version is CORRECTED to fix the column resolution error.-- DATE RANGE: February 1, 2025, and onward.WITH-- Part 1: Calculate the Daily Market Price of WELL (Our reliable benchmark)daily_market_price AS (WITH well_weth_daily_price AS (SELECT DATE_TRUNC('day', block_time) AS day, APPROX_PERCENTILE(CASE WHEN token_bought_address = 0xA88594D404727625A9437C3f886C7643872296AE THEN token_sold_amount / token_bought_amount WHEN token_sold_address = 0xA88594D404727625A9437C3f886C7643872296AE THEN token_bought_amount / token_sold_amount END, 0.5) AS well_in_wethFROM dex.trades WHERE blockchain = 'base' AND ((token_bought_address = 0xA88594D404727625A9437C3f886C7643872296AE AND token_sold_address = 0x4200000000000000000000000000000000000006) OR (token_bought_address = 0x4200000000000000000000000000000000000006 AND token_sold_address = 0xA88594D404727625A9437C3f886C7643872296AE)) AND token_bought_amount > 0 AND token_sold_amount > 0 GROUP BY 1),weth_daily_price AS (SELECT DATE_TRUNC('day', minute) AS day, APPROX_PERCENTILE(price, 0.5) AS weth_in_usdFROM prices.usd WHERE contract_address = 0x4200000000000000000000000000000000000006 AND blockchain = 'base' GROUP BY 1)SELECT w.day, w.weth_in_usd * p.well_in_weth AS market_price_usd FROM weth_daily_price w JOIN well_weth_daily_price p ON w.day = p.day),-- Part 2: Calculate the Daily Implied Auction Price of WELL using ONLY stablecoin auctions.daily_auction_price AS (WITH all_stablecoin_bids AS (-- *** THE FIX IS HERE: We filter each table BEFORE the UNION ALL ***-- Get all stablecoin bids from BaseSELECTblock_time,varbinary_to_uint256(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_well_in,varbinary_to_uint256(SUBSTRING(data FROM 33 FOR 32)) / 1e6 AS value_of_reserves_out_usdFROM base.logsWHERE varbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672cAND contract_address = 0x89b89c30E2f60Bd30059c3924eF5b8c0Fcd1B64A -- USDBC on BaseUNION ALL-- Get all stablecoin bids from OptimismSELECTblock_time,varbinary_to_uint256(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_well_in,varbinary_to_uint256(SUBSTRING(data FROM 33 FOR 32)) / 1e6 AS value_of_reserves_out_usdFROM optimism.logsWHERE varbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672cAND contract_address = 0x475d7c6999dc27E640d260aBf9f2fA9333E472CF -- USDC on Optimism)SELECTDATE_TRUNC('day', block_time) AS day,SUM(value_of_reserves_out_usd) / SUM(amount_well_in) AS implied_auction_price_usdFROM all_stablecoin_bidsWHERE amount_well_in > 0 AND block_time >= CAST('2025-02-01' AS TIMESTAMP)GROUP BY 1),-- Step 3: Combine the daily data and assign an epoch ID to each 14-day auction cycle.historical_data AS (SELECT-- We define an "epoch" as a 14-day period, anchored to a known auction start date.FLOOR(DATE_DIFF('day', CAST('2025-02-18' AS TIMESTAMP), a.day) / 14) AS epoch_id,a.day,m.market_price_usd,a.implied_auction_price_usdFROM daily_auction_price a