Query editor
-- This query analyzes the frequency of participation in Moonwell's Reserve Auctions-- to determine how many bidders are one-time participants versus repeat "power users".-- This version is corrected to fix the column resolution error.-- DATE RANGE: February 1, 2025, and onward.WITH-- Step 1: Gather every successful bid from all auction markets on both Base and Optimism.-- This is now done by filtering each chain's logs table first, then unioning them.all_bids AS (-- Get all bids from BaseSELECT topic1 AS bidder_addressFROM base.logsWHEREvarbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672c -- ReservesAcquired eventAND 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 OptimismSELECT topic1 AS bidder_addressFROM optimism.logsWHEREvarbinary_ltrim(topic0) = 0xe566118251a7af580a18e9ae7f986da216a7f0429315bb06864fc5ab9e4e672c -- ReservesAcquired eventAND 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: Count the number of bids made by each unique bidder.bid_counts_per_address AS (SELECTbidder_address,COUNT(*) AS number_of_bidsFROM all_bidsGROUP BY 1)-- Final Step: Group the bidders into categories based on how many times they have participated.SELECTCASEWHEN number_of_bids = 1 THEN '1. One-Time Bidders'WHEN number_of_bids >= 2 AND number_of_bids <= 5 THEN '2. Casual Repeat Bidders (2-5 Bids)'WHEN number_of_bids >= 6 AND number_of_bids <= 20 THEN '3. Regular Bidders (6-20 Bids)'WHEN number_of_bids > 20 THEN '4. Power Users / Bots (>20 Bids)'END AS bidder_category,COUNT(bidder_address) AS number_of_biddersFROM bid_counts_per_addressGROUP BY 1ORDER BY 1