Query editor
-- =============================================================================-- Project RECLAIM: wallet-discovery enumeration (Tier 1)-- https://dune.com/queries/8191456---- Parameters:-- {{pool_addresses}} = comma-separated 0x contract addresses to scan---- Output rows (column names matter; dune.ts maps them):-- wallet_address = candidate wallet (0x hex, lowercase)-- contract_address = for 'holder' rows: the target token contract;-- for 'staker' rows: the staking contract (the target)-- chain = ethereum | arbitrum | optimism | polygon | bnb | base-- position_type = 'holder' (holds the token) | 'staker' (deposited into it)-- balance_wei = raw wei balance / net deposited (historical)-- staking_contract = for 'staker' rows: the staking contract (0x hex)-- deposited_token = for 'staker' rows: the token deposited into the staking-- contract (0x hex). Used to attribute a pid via a live-- poolInfo() scan during verification.---- NOTE: valuation is intentionally NOT computed here. Balances are re-verified-- live on-chain and priced by DefiLlama in the backend (see lib/verify.ts).-- Withdrawals are not subtracted from the deposit heuristic; live userInfo()-- reads are the source of truth and drop stale candidates.---- `holders` DOES net incoming against outgoing transfers (unlike `depositors`,-- where netting isn't meaningful because a MasterChef-style withdrawal isn't-- a same-token Transfer event). This matters because the LIMIT 1000 cap below-- ranks by balance_wei: a wallet that received a huge amount once and later-- sent it all away must not outrank a wallet with a smaller real balance.-- =============================================================================WITH target_contract AS (SELECT from_hex(replace(trim(str), '0x', '')) AS pool_addressFROM UNNEST(SPLIT('{{pool_addresses}}', ',')) AS t(str)WHERE str != ''),-- Net holders of each target token (LP / token holders), all chains: sum of-- incoming transfers minus outgoing transfers, per wallet per contract.holders AS (SELECT chain, wallet_address, contract_address, 'holder' AS position_type, SUM(delta) AS balance_weiFROM (SELECT 'ethereum' AS chain, "to" AS wallet_address, lower(to_hex(contract_address)) AS contract_address, value AS deltaFROM erc20_ethereum.evt_TransferWHERE contract_address IN (SELECT pool_address FROM target_contract)UNION ALLSELECT 'ethereum' AS chain, "from" AS wallet_address, lower(to_hex(contract_address)) AS contract_address, -value AS deltaFROM erc20_ethereum.evt_TransferWHERE contract_address IN (SELECT pool_address FROM target_contract)UNION ALLSELECT 'arbitrum' AS chain, "to", lower(to_hex(contract_address)), valueFROM erc20_arbitrum.evt_TransferWHERE contract_address IN (SELECT pool_address FROM target_contract)UNION ALLSELECT 'arbitrum' AS chain, "from", lower(to_hex(contract_address)), -valueFROM erc20_arbitrum.evt_TransferWHERE contract_address IN (SELECT pool_address FROM target_contract)UNION ALLSELECT 'optimism' AS chain, "to", lower(to_hex(contract_address)), valueFROM erc20_optimism.evt_Transfer