Query editor
-- This query visualizes the historical difference between the daily market price of WELL-- and the daily average implied price of WELL from the stablecoin (USDC/USDBC) reserve auctions.-- This version is CORRECTED to fix the column resolution error.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 ***SELECTblock_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 ALLSELECTblock_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)-- Final Step: Join the two price series for visualization.SELECTCOALESCE(m.day, a.day) AS day,m.market_price_usd,a.implied_auction_price_usdFROM daily_market_price mFULL OUTER JOIN daily_auction_price a ON m.day = a.dayWHERE COALESCE(m.day, a.day) >= CAST('2025-02-01' AS TIMESTAMP)ORDER BY 1 ASC