Query editor
-- This query calculates the total WELL volume bid by each participant and groups smaller-- participants into an "Others" category for a clean pie chart visualization.-- This version is CORRECTED to fix the column resolution error.-- DATE RANGE: February 1, 2025, and onward.WITH-- Step 1: Get all bids and decode the WELL amount for each, filtering each chain BEFORE the union.all_bids AS (-- Get all bids from BaseSELECTtopic1 AS bidder_address,varbinary_to_uint256(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_wellFROM base.logsWHEREvarbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672cAND block_time >= CAST('2025-02-01' AS TIMESTAMP)AND contract_address IN (0x9f2ca3c6Cd1dddb7aD473a0a893C3104E2af15Ad, 0x8373155335839e3D078f3F224E8B6618Fc26eF17, 0x89b89c30E2f60Bd30059c3924eF5b8c0Fcd1B64A, 0x064D8Cb3B7a22F4cFBdd602eBC7E722Bb71405D8, 0x48bc4876D33Db30929c373c3B949b66CB8d641F3, 0xbd22DaFeF550094A32f388CD256FE133a0A14387, 0xEfE30785362225106367039971d82715dcB35192, 0xc7840e86A0aa22c23BCbC153CE61f6009733bf2C, 0x75494780E76bB41c0fDf29DBA4b2Ce82501c12b0, 0x83D37e3df05F1507667AF4dfc83EC8A38Cf2dA08, 0x7bBe5972e01BAc64fE3AD7EFfBa6D164f0a1F15f, 0xe34D7D109B97e1b1DAc9A9920e6A6769814Ac7eE, 0xA078017f827DC7B8540C98A3bF7b2153B2aF6cB3, 0x84C74431200Bcd3Ba4b557024734891857b43354, 0xf8f7b937a4CC6Cc16b600B3611ce0c1152a5b3F9)UNION ALL-- Get all bids from OptimismSELECTtopic1 AS bidder_address,varbinary_to_uint256(SUBSTRING(data FROM 1 FOR 32)) / 1e18 AS amount_wellFROM optimism.logsWHEREvarbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672cAND block_time >= CAST('2025-02-01' AS TIMESTAMP)AND contract_address IN (0x475d7c6999dc27E640d260aBf9f2fA9333E472CF, 0x9E58891D8DF4e6Dd8bAfD3082A59B72C51202841, 0xE6Aea947c0F082c5Dc751BB9C7f44Ce059590962, 0x080D64570a58FF87E14CC5Cb91d1aaB26b15CFDc, 0x8455D94e412A498Df8727D904252892Fb111a4cD, 0x01c369a6238226702E48C9C3fBB1de33F4b05D74, 0x9E530e9F3f9b1046e223cc3eB97fA0bBab5Dd993, 0x3B40085872eaEA59CF39FCafFb3dc36085aE48f6, 0xFfF466528fE1a18b95Fa910C96540A70EC2727FB, 0x6427D36153dE11b694d70604B0715790769024f7, 0x589F59fBDB5952920fA557c924F6f5CFf184b155, 0x78a9C06188195CEE3cBf67303a1708cb8765b9ec)),-- Step 2: Sum the total volume for each bidder and rank them.bidder_volume_ranked AS (SELECTbidder_address,SUM(amount_well) AS total_volume,RANK() OVER (ORDER BY SUM(amount_well) DESC) AS rankFROM all_bidsGROUP BY 1)-- Final Step: Group the top 10 bidders and aggregate the rest into "Others".SELECTCASEWHEN rank <= 10 THEN CAST(bidder_address AS VARCHAR) -- Show the address for the top 10ELSE 'Others'END AS bidder,SUM(total_volume) AS total_well_volumeFROM bidder_volume_rankedGROUP BY 1ORDER BY 2 DESC