finddalao_sol/src/code/dune_token_owner_transfer_count.sql

-- 记录一个token的在某段时间的transfer  次数（count） 最多的wallet ，便于查看这些wallet是什么 ，以后作为过滤address

WITH
  Exclude_onwer (owner) AS (
  VALUES
      ('5Q544fKrFoe6tsEbD7S8EmxGTJYAKtTVhAW5Q5pge4j1'), --  Raydium Authority V4
      ('BQ72nSv9f3PRyRKCBnHLVrerrv37CYTHm5h3s9VSGQDV'), -- Jupiter Aggregator Authority 1
      ('2MFoS3MPtvyQ4Wh4M9pdfPjz6UhVoNbFbGJAskCPCj3h'), -- Jupiter Aggregator Authority 2
      ('HU23r7UoZbqTUuh3vA7emAGztFtqwTeVips789vqxxBw'), -- Jupiter Aggregator Authority 3
      ('3CgvbiM3op4vjrrjH2zcrQUwsqh5veNVRjFCB9N6sRoD'), -- Jupiter Aggregator Authority 4
      ('6LXutJvKUw8Q5ue2gCgKHQdAN4suWW8awzFVC6XCguFx'), -- Jupiter Aggregator Authority 5
      ('CapuXNQoDviLvU1PxFiizLgPNQCxrsag1uMeyk6zLVps'), -- Jupiter Aggregator Authority 6
      ('GGztQqQ6pCPaJQnNpXBgELr5cs3WwDakRbh1iEMzjgSJ'), -- Jupiter Aggregator Authority 7
      ('9nnLbotNTcUhvbrsA6Mdkx45Sm82G35zo28AqUvjExn8'), -- Jupiter Aggregator Authority 8
      ('3LoAYHuSd7Gh8d7RTFnhvYtiTiefdZ5ByamU42vkzd76'), -- Jupiter Aggregator Authority 9
      ('DSN3j1ykL3obAVNv7ZX49VsFCPe4LqzxHnmtLiPwY6xg'), -- Jupiter Aggregator Authority 10
      ('69yhtoJR4JYPPABZcSNkzuqbaFbwHsCkja1sP1Q2aVT5'), -- Jupiter Aggregator Authority 11
      ('6U91aKa8pmMxkJwBCfPTmUEfZi6dHe7DcFq2ALvB2tbB'), -- Jupiter Aggregator Authority 12
      ('7iWnBRRhBCiNXXPhqiGzvvBkKrvFSWqqmxRyu9VyYBxE'), -- Jupiter Aggregator Authority 13
      ('4xDsmeTWPNjgSVSS1VTfzFq3iHZhp77ffPkAmkZkdu71'), -- Jupiter Aggregator Authority 14
      ('GP8StUXNYSZjPikyRsvkTbvRV1GBxMErb59cpeCJnDf1'), -- Jupiter Aggregator Authority 15
      ('HFqp6ErWHY6Uzhj8rFyjYuDya2mXUpYEk8VW75K9PSiY'), -- Jupiter Aggregator Authority 16
      ('45ruCyfdRkWpRNGEqWzjCiXRHkZs8WXCLQ67Pnpye7Hp'), -- Jupiter Partner Referral Fee Vault
      ('ZG98FUCjb8mJ824Gbs6RsgVmr1FhXb2oNiJHa2dwmPd')  -- CxvksNjwhdHDLr3qbCXNKVdeYACW8cs93vFqLqtgyFE5's fee account
      ,('GE5PnwZTgWw1YqVGno2QPWqie6aLjswNeGFhiF1nDg3k')  --Raydium (8008-USDC) Market
      ,('DGT9TPRGQwpJeMvDdZJDEaVRZa2ZMXtp16pGZgAgBoLx') --Pump.fun (8008) Bonding Curve
      ,('25mYnjJ2MXHZH6NvTTdA63JvjgRVcuiaj6MRiEQNs1Dq')  -- okx 相关
      ,('14qLxu9XDQtc2pgnxX2SSdWPpnuVrEqccgwmATH1khcY')  -- Raydium (SOL-8008) Market
      ,('5YET3YapxD6to6rqPqTWB3R9pSbURy6yduuUtoZkzoPX')  -- Jupiter ApePro Keeper
    ,('F5sw1r94VXmUGwesPrcY3TvwSrfVAZmRaWwMtD1tr3Yf')  -- Meteora (8008-SOL) Market   
    ,('j1oAbxxiDUWvoHxEDhWE7THLjEkDQW2cSHYn2vttxTF') --jupiter limit order taker account
     ,('4KaVawPKmG1RZCv698XD9YzRsJcFPXrEoYnZbdZmfFFq')  --okx相关
     
      ,('j1oeQoPeuEDmjvyMwBmCWexzCQup77kbKKxV59CnYbd') --jupiter limit order taker account
       ,('6KFVj2czLwNQeE4yBgFau6LHUS72vqgP11Sv1JUNxghz')  --fee account
        ,('j1oAbxxiDUWvoHxEDhWE7THLjEkDQW2cSHYn2vttxTF') 
      

      
      
  ),
  DistinctPairs AS (
    SELECT DISTINCT
      from_owner,
      to_owner
    FROM
      tokens_solana.transfers
    WHERE
      --   block_date > (TIMESTAMP '2024-08-01 00:00:00 +08:00')
      block_date >= (TIMESTAMP '{{begin_date}}' )
      AND block_date < (TIMESTAMP '{{end_date}}' )
      AND token_mint_address = '5puhwnyz2Tv8jSmmBD5DSqCwFVXwwPGZacymM7DQpump'
      AND action = 'transfer'
      AND (
        from_owner NOT IN (
          SELECT
            owner
          FROM
            Exclude_onwer
        )
        AND to_owner NOT IN (
          SELECT
            owner
          FROM
            Exclude_onwer
        )
      )
  ),
  CombinedValues AS (
    SELECT
      from_owner AS combined_values
    FROM
      DistinctPairs
    UNION ALL
    SELECT
      to_owner AS combined_values
    FROM
      DistinctPairs
  )
  
SELECT
  combined_values,
  COUNT(*) AS total_count
FROM
  CombinedValues
GROUP BY
  combined_values
HAVING
  COUNT(*) > 10
ORDER BY
  total_count DESC