finddalao_sol/src/code/dune_get_relation_sol_transfer.sql

-- 思路 获取一段时间的 transfer 数据 根据dextrade 或者 其他筛选 先排除一部分 tx_id 在groupby tx_id 进一步筛选 最后 成功
-- 到了 先排除一部分 就时间太长了
with
  input_owner (address) as (
    values
      ('DZuiugsZ8bJNLYnkLE3aK6Fyj3PdGEeev2kUQ9htk1No'),
      ('FoRrce2AqaZAhPZozrH49835VueVj31Vq4hRQNtt1ao7'),
      ('7PXAh12kVHji6FRdtpnZprZog9zC5aboVZRt5UnP961n'),
      ('4ZStjfNTvTDHGsBGqQLTfXvtALMHwnfpUEdbnRmgbPVm'),
      ('62GvRgevD9g93xoyV8SjFL5VKF2h7YhCvJWiLzfNm8uC'),
      ('AgPsPtpAiQqMtcmSsGEGvje1dXTco3NGi1gAF7gvYC7L'),
      ('BXvEmdFHrSCDE1DYg47U1SgW6juGtSHfx32ijpM5oq23')
  ),
  exclude_address (address) as (
    values
      ('AVzP2GeRmqGphJsMxWoqjpUifPpCret7LqWhD8NWQK49'), -- Jupiter Labs Perpetuals Vault Authority
      ('A2rCQdCqQB4wu72x9Q7iq1QugtdZmcyWsiSPfgfZzu2u'), -- Jupiter Perpetuals Keeper 1
      ('DFZcDnmEYNUK1khquZzx5dQYiEyjJ3N5STqaDVLZ88ZU'), -- Jupiter Perpetuals Keeper 2
      ('PERPHjGBqRHArX4DySjwM6UJHiR3sWAatqfdBS2qQJu'), -- Jupiter Labs Perpetuals
      ('BUvduFTd2sWFagCunBPLupG8fBTJqweLw9DuhruNFSCm'), -- Jupiter Labs Perpetuals (SOL) Vault
      ('WzWUoCmtVv7eqAbU3BfKPU3fhLP6CXR8NCJH78UK9VS'), -- Jupiter Labs Perpetuals (USDC) Vault
      ('Gex24YznvguMad1mBzTQ7a64U1CJy59gvsStQmNnnwAd'), -- Jupiter Labs Perpetuals (USDT) Vault
      ('Bgarxg65CEjN3kosjCW5Du3wEqvV3dpCGDR3a2HRQsYJ'), -- Jupiter Labs Perpetuals (ETH) Vault
      ('FgpXg2J3TzSs7w3WGYYE7aWePdrxBVLCXSxmAKnCZNtZ'), -- Jupiter Labs Perpetuals (BTC) Vault
      ('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
      ('jupoNjAxXgZ4rjzxzPMP4oxduvQsQtZzyknqvzYNrNu'), --  Jupiter Limit Order
      ('Cw8CFyM9FkoMi7K7Crf6HNQqf4uEMzpKw6QNghXLvLkY'), -- Jitotip 3
      ('j1o2qRpjcyUwEvwtcfhEQefh773ZgjxcVRry7LDqg5X'), -- Jupiter Limit Order V2
      ('DD3AeAssFvjqTvRTrRAtpfjkBF8FpVKnFuwnMLN9haXD') -- Marginfi Bank Liquidity (SOL) Vault  Authority
      
      --   ["DSwpgjMvXhtGn6BsbqmacdBZyfLj6jSWf3HJpdJtmg6N", "Dexlab"],
      --   ["BSwp6bEBihVLdqJRKGgzjcGLHkcTuzmSo1TQkHepzH8p", "Bonkswap"],
      --   ["Eo7WjKq67rjJQSZxS6z3YkapzY3eMj6Xy8X5EQVn5UaB", "Meteora"],
      --   ["FLUXubRmkEi2q6K3Y9kBPg9248ggaZVsoSFhtJHSrm1X", "FluxBeam"],
      --   ["HyaB3W9q6XdA5xwpU4XnSZV94htfmbmqJXZcEbRaJutt", "Invariant"],
      --   ["PERPHjGBqRHArX4DySjwM6UJHiR3sWAatqfdBS2qQJu", "Perps"],
      --   ["treaf4wWBBty3fHdyBpo35Mz84M8k3heKXmjmi9vFt5", "Helium Network"],
      --   ["DjVE6JNiYqPL2QXyCUUh8rNjHrbz9hXHNYt99MQ59qw1", "Orca V1"],
      --   ["CURVGoZn8zycx6FXwwevgBTB2gVvdbGTEpvMJDbgs2t4", "Aldrin V2"],
      --   ["LBUZKhRxPF3XUpBCjp4YzTKgLccjZhTSDM9YuVaPwxo", "Meteora DLMM"],
      --   ["PhoeNiXZ8ByJGLkxNfZRnkUfjvmuYqLR89jjFHGqdXY", "Phoenix"],
      --   ["SSwapUtytfBdBn1b9NUGG6foMVPtcWgpRU32HToDUZr", "Saros"],
      --   ["AMM55ShdkoGRB5jVYPjWziwk8m5MpwyDgsMWHaMSQWH6", "Aldrin"],
      --   ["9tKE7Mbmj4mxDjWatikzGAtkoWosiiZX9y6J4Hfm2R8H", "Oasis"],
      --   ["MERLuDFBMmsHnsBPZw2sDQZHvXFMwp8EdjudcU2HKky", "Mercurial"],
      --   ["SwaPpA9LAaLfeLi3a68M4DjnLqgtticKg6CnyNwgAC8", "Token Swap"],
      --   ["srmqPvymJeFKQ4zGQed1GFppgkRHL9kaELCbyksJtPX", "Openbook"],
      --   ["CLMM9tUoggJu2wagPkkqs9eFG4BWhVBZWkP1qv3Sp7tR", "Crema"],
      --   ["DecZY86MU5Gj7kppfUCEmd4LbXXuyZH1yHaP2NTqdiZB", "Saber (Decimals)"],
      --   ["CTMAxxk34HjKWxQ3QLZK1HpaLXmBveao3ESePXbiyfzh", "Cropper"],
      --   ["CAMMCzo5YL8w4VFF8KVHrK22GGUsp5VTaW7grrKgrWqK", "Raydium CLMM"],
      --   ["whirLbMiicVdio4qvUfM5KAg6Ct8VwpYzGff3uctyCc", "Whirlpool"],
      --   ["PSwapMdSai8tjrEXcxFeQth87xC4rRsa4VA5mhGhXkP", "Penguin"],
      --   ["EewxydAPCCVuNEyrVN68PuSYdQ7wKn27V9Gjeoi8dy3S", "Lifinity V1"],
      --   ["C1onEW2kPetmHmwe74YC1ESx3LnFEpVau6g2pg4fHycr", "Clone Protocol"],
      --   ["2wT8Yq49kHgDzXuPxZSaeLaH1qbmGXtEyPy64bL7aD3c", "Lifinity V2"],
      --   ["Dooar9JkhdZ7J3LHN3A7YCuoGRUggXhQaG4kijfLGU2j", "StepN"],
      --   ["9W959DqEETiGZocYWCQPaJ6sBmUzgfxXfqGeTEdp3aQP", "Orca V2"],
      --   ["opnb2LAfJYbRMAHHvqjCwQxanZn7ReEHp1k81EohpZb", "OpenBook V2"],
      --   ["MarBmsSgKXdrN1egZf5sqe1TMai9K1rChYNDJgjq7aD", "Marinade"],
      --   ["GFXsSL5sSaDfNFQUYsHekbWBW1TsFdjDYzACh62tEHxn", "GooseFX"],
      --   ["SSwpkEEcbUqx4vtoEByFjSkhKdCT862DNVb52nZg1UZ", "Saber"],
      --   ["stkitrT1Uoy18Dk1fTrgPw8W6MVzoCfYoAFT4MLsmhq", "Sanctum"],
      --   ["675kPX9MHTjS2zt1qfr1NYHuzeLXfQM9H24wFSUt1Mp8", "Raydium"],
  ),
  trade as (
    SELECT
      tx_id
    from
      dex_solana.trades
    where
      block_time > CAST('{{begin_date_01}}' AS TIMESTAMP)
      and block_time <= CAST('{{end_date_01}}' AS TIMESTAMP)
  ),
  -- temp_01 可以获得非swap 的 input_owner 参与的transfer 交易 (不是一笔交易的全部  一笔交易可能实际非swap 但会有多个transfer mint 等)
  temp_01 as (
    SELECT
      transfer.*
      --   tx_id,
      --   block_date,
      --   amount,
      --   action,
      --   token_mint_address,
      --   from_owner,
      --   to_owner,
      --   from_token_account,
      --   to_token_account
    FROM
      (
        SELECT
          tx_id,
          block_date,
          amount,
          action,
          token_mint_address,
          from_owner,
          to_owner,
          from_token_account,
          to_token_account
        FROM
          tokens_solana.transfers
        where
          block_date > CAST('{{begin_date_02}}' AS TIMESTAMP)
          and block_date <= CAST('{{end_date_02}}' AS TIMESTAMP)
      ) transfer
      inner join input_owner on (
        input_owner.address = transfer.from_owner
        or input_owner.address = transfer.to_owner
      )
      -- 非swap 的交易
      left join trade on trade.tx_id = transfer.tx_id
    WHERE
      trade.tx_id is null
  ),
  -- 下一步 temp_02 有连个方案 
  -- 1. groupby tx_id 去除 一些是 none action 含有mint等非 ，mint_token_address为非 sol等的tx_id  获取到真正的tranfer 交易 然后获取他们的 relation owner
  -- 1. 缺点 明显太复杂 不知道时间能否允许不 
  -- 2.  不用groupby 用union all 获取 exclude_ids  然后获取真正的 relation owner
  --   exclude_tx_ids as (
  --     SELECT
  --       tx_id
  --     from
  --       temp_01
  --     where
  --       action != 'transfer'
  --     UNION all
  --     SELECT
  --       tx_id
  --     from
  --       temp_01
  --     where
  --       token_mint_address != 'So11111111111111111111111111111111111111112'
  --     UNION all
  --     SELECT
  --       tx_id
  --     from
  --       temp_01
  --     where
  --       from_owner is null
  --       or to_owner is null
  --     UNION all
  --     SELECT
  --       temp_01.tx_id as tx_id
  --     from
  --       temp_01
  --       left join exclude_address on temp_01.from_owner = exclude_address.address
  --       or temp_01.to_owner = exclude_address.address
  --     where
  --       exclude_address.address is null
  --   )
  --   temp_02 as (
  --     select
  --       temp_01.*
  --     from
  --       temp_01
  --       left join exclude_tx_ids on temp_01.tx_id = exclude_tx_ids.tx_id
  --     where
  --       exclude_tx_ids.tx_id is null
  --   )
  temp_02 as (
    SELECT
      tx_id
    from
      temp_01
    GROUP BY
      tx_id
    HAVING
      SUM(
        CASE
          WHEN action = 'transfer' THEN 1
          ELSE 0
        END
      ) > 0
      and COUNT(DISTINCT COALESCE(token_mint_address, 'NULL')) = 1
      and COUNT(DISTINCT COALESCE(action, 'NULL')) = 1
      and (
        SUM(
          CASE
            WHEN token_mint_address = 'So11111111111111111111111111111111111111112' THEN 1
            ELSE 0
          END
        ) > 0
        and sum(amount) >= 0.5 * 1e9
      )
      and SUM(
        CASE
          WHEN from_owner is null THEN 1
          ELSE 0
        END
      ) = 0
      and SUM(
        CASE
          WHEN to_owner is null THEN 1
          ELSE 0
        END
      ) = 0
  )
SELECT
  -- DISTINCT 
  temp_02.tx_id as tx_id,
  action,
  from_owner,
  to_owner,
  from_token_account,
  to_token_account,
  amount
from
  temp_02
  inner join temp_01 on temp_01.tx_id = temp_02.tx_id