Query editor
-- This query creates a historical dataset comparing daily market price vs. daily auction volume.-- DATE RANGE: February 1, 2025, and onward.WITHdaily_auction_volume AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(varbinary_to_uint256(SUBSTRING(data FROM 1 FOR 32)) / 1e18) AS total_well_bid_in_auctionsFROM (SELECT block_time, data FROM base.logs WHERE varbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672cUNION ALLSELECT block_time, data FROM optimism.logs WHERE varbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672c)WHERE block_time >= CAST('2025-02-01' AS TIMESTAMP) -- DATE FILTER ADDEDGROUP BY 1),daily_market_price AS (-- This CTE is unchanged as it calculates price for all daysWITH 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 well_market_price_usd FROM weth_daily_price w JOIN well_weth_daily_price p ON w.day = p.day)SELECTprice.day,price.well_market_price_usd,COALESCE(volume.total_well_bid_in_auctions, 0) AS total_well_bid_in_auctionsFROM daily_market_price priceLEFT JOIN daily_auction_volume volume ON price.day = volume.dayWHERE price.day >= CAST('2025-02-01' AS TIMESTAMP) -- DATE FILTER MODIFIEDORDER BY price.day ASC