Query editor
-- WSOLAddress: 'So11111111111111111111111111111111111111112'-- FeeWallet: 97VmzkjX9w8gMFS2RnHTSjtMEDbifGXBq9pgosFdFnM-- FirstTradeTimestamp: TIMESTAMP '2024-11-27'WITHbotTradesWithFees AS (SELECTdate_trunc('day', block_time) AS block_date,type,amount_usd,fee_usd,fee_token_amount,fee_token_symbol,user,project_contract_address,tx_idFROM/* Trdewiz Trading Bot Trades */query_4391193WHEREisLastTradeInTransaction = true -- This prevents over counting for multi-hop trades and excludes tax distributions),firstUserOccurrences AS (SELECTuser,MIN(block_date) AS firstTradeDateFROMbotTradesWithFeesGROUP BYuser)SELECTblock_date,SUM(amount_usd) AS totalVolumeUSD,COUNT(DISTINCT (botTradesWithFees.user)) AS numberOfUsers,COALESCE(COUNT(DISTINCT (firstUserOccurrences.user)), 0) AS numberOfNewUsers,SUM(IF("type" = 'Buy', amount_usd, 0)) AS buyVolumeUSD,SUM(IF("type" = 'Sell', amount_usd, 0)) AS sellVolumeUSD,SUM(fee_usd) AS feesUSD,SUM(IF(fee_token_symbol = 'SOL', fee_token_amount, 0)) AS feesSOL,COUNT(DISTINCT (tx_id)) AS numberOfTrades,COUNT(DISTINCT (project_contract_address)) AS numberOfPairs,COUNT(DISTINCT (botTradesWithFees.user)) - COALESCE(COUNT(DISTINCT (firstUserOccurrences.user)), 0) AS numberOfReturningUsers,SUM(COALESCE(COUNT(DISTINCT (firstUserOccurrences.user)), 0)) OVER (ORDER BYblock_date) AS cumulative_numberOfNewUsersFROMbotTradesWithFeesLEFT OUTER JOIN firstUserOccurrences ON (botTradesWithFees.user = firstUserOccurrences.userAND botTradesWithFees.block_date = firstUserOccurrences.firstTradeDate)GROUP BYblock_dateORDER BYblock_date DESC