DuneAnalyitcsで特定のタイミングでのERC20トークンホルダー、保有トークン量を出す

どうも、minicooheiです。
Ethereum Advent Calender 11日目ということでDeFiNer,NFT,やっている人に実用的な知識を。

特定のタイミングでのトークンホルダー、保有量を抽出

AirDropの対象者を抽出したり、オンチェーン分析、納税用の計算でこのタイミングでこのトークンいくらもってたっけ？　みたいになることありませんか。　僕はありました。

Etherscanで瞬間瞬間のホルダーのスナップショットは取れたり、ETHの値のヒストリー値はわかるのですが、なかなか既存のサービスだと抽出が難しいと思います。

そこで、今回は比較的簡単にEthereum mainnetでのトークンホルダーのリストを抽出する方法と保有量をとる方法をしますご紹介。

頼りになるDune Anayltics

Dune Analyticsとは、　Ethereumなどのブロックチェーン上のデータを分析するツールを提供してくれるサービスです。無料の場合はパブリック前提ですが、データを見たいだけの場合は無料で利用することができます。

では時間もないので早速抽出方法を。ちなみに Duneはアドレスの記法が特殊なのでそれはここを見てください。

Sample

WITH addresses AS
  ( SELECT "to" AS adr
   FROM erc20."ERC20_evt_Transfer" tr
   WHERE contract_address = '/x0000000000' ) ,
     transfers AS
  ( SELECT DAY,
           address,
           token_address,
           sum(amount) AS amount -- 1日あたりどの程度変動するか

   FROM
     ( SELECT date_trunc('day', evt_block_time) AS DAY,
              "to" AS address,
              tr.contract_address AS token_address,
              value AS amount
      FROM erc20."ERC20_evt_Transfer" tr --該当アドレスの to のTxを取得 

      WHERE  date_trunc('day', evt_block_time) < '{{timerange}}' and contract_address = CONCAT('\x', substring('{{Token Address}}'
                                                      FROM 3))::bytea --Token address 

      UNION ALL SELECT date_trunc('day', evt_block_time) AS DAY,
                       "from" AS address,
                       tr.contract_address AS token_address, -value AS amount
      FROM erc20."ERC20_evt_Transfer" tr ----該当アドレスの from のTxを取得 

      WHERE date_trunc('day', evt_block_time) < '{{timerange}}' and contract_address = CONCAT('\x', substring('{{Token Address}}'
                                                      FROM 3))::bytea --Token address
 ) t
   GROUP BY 1,
            2,
            3 ) ,
     balances_with_gap_days AS
  ( SELECT t.day,
           address,
           SUM(amount) OVER (PARTITION BY address
                             ORDER BY t.day) AS balance, -- 毎日のトークン量
 lead(DAY, 1, now()) OVER (PARTITION BY address
                           ORDER BY t.day) AS next_day -- 翌日のトークン量

   FROM transfers t ) ,
     days AS
  ( SELECT generate_series('2016-01-20'::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY -- 2016-01-20からの累計を取得(もっと新しくてもOK)
 ) ,
     balance_all_days AS
  ( SELECT d.day,
           address,
           SUM(balance/10^0) AS balance
   FROM balances_with_gap_days b
   INNER JOIN days d ON b.day <= d.day
   AND d.day < b.next_day -- 最初のトランスファー後、1日ごとの結果を得る
 --INNER JOIN erc20.tokens erc ON b.token_address = erc.contract_address

   GROUP BY 1,
            2
   ORDER BY 1,
            2 )

SELECT 
       distinct(address) AS "Holders",
       sum(balance)/10^18 as balance　--decimal が18でないトークンはここを書き換える必要がある
       
FROM balance_all_days b
WHERE balance > 0 and b.day= date_trunc('day','{{timerange}}':: TIMESTAMP) -- and address='\xccB82218c6F82a2B750Cf0D65e21AE6eAE14070c'
                                                      
GROUP BY 1
ORDER BY 1 ;

このクエリを使って、変数に、0xD69F306549e9d96f183B1AecA30B8f4353c2ECC3,2021-12-10
動かしてもらえるとMCHCの12月10日時点のホルダー数が表示されると思います。

etherscan

さらに下部にある

-- and address='\xccB82218c6F82a2B750Cf0D65e21AE6eAE14070c' --ここをコメントイン

ここをコメントインしてもらえると各アドレス単位で、ある日付でのトークン保有量も出すことができます。

保有量の推移

保有量の推移もほとんど同じクエリで抽出できます。
Sample

WITH addresses AS
  ( SELECT "to" AS adr
   FROM erc20."ERC20_evt_Transfer" tr
   WHERE contract_address = '/x0000000000' ) ,
     transfers AS
  ( SELECT DAY,
           address,
           token_address,
           sum(amount) AS amount -- 1日あたりどの程度変動するか

   FROM
     ( SELECT date_trunc('day', evt_block_time) AS DAY,
              "to" AS address,
              tr.contract_address AS token_address,
              value AS amount
      FROM erc20."ERC20_evt_Transfer" tr --該当アドレスの to のTxを取得 

      WHERE  date_trunc('day', evt_block_time) < '{{timerange}}' and contract_address = CONCAT('\x', substring('{{Token Address}}'
                                                      FROM 3))::bytea --Token address 

      UNION ALL SELECT date_trunc('day', evt_block_time) AS DAY,
                       "from" AS address,
                       tr.contract_address AS token_address, -value AS amount
      FROM erc20."ERC20_evt_Transfer" tr ----該当アドレスの from のTxを取得 

      WHERE date_trunc('day', evt_block_time) < '{{timerange}}' and contract_address = CONCAT('\x', substring('{{Token Address}}'
                                                      FROM 3))::bytea --Token address
 ) t
   GROUP BY 1,
            2,
            3 ) ,
     balances_with_gap_days AS
  ( SELECT t.day,
           address,
           SUM(amount) OVER (PARTITION BY address
                             ORDER BY t.day) AS balance, -- 毎日のトークン量
 lead(DAY, 1, now()) OVER (PARTITION BY address
                           ORDER BY t.day) AS next_day -- 翌日のトークン量

   FROM transfers t ) ,
     days AS
  ( SELECT generate_series('2016-01-20'::TIMESTAMP, date_trunc('day', NOW()), '1 day') AS DAY -- 2016-01-20からの累計を取得(もっと新しくてもOK)
 ) ,
     balance_all_days AS
  ( SELECT d.day,
           address,
           SUM(balance/10^0) AS balance
   FROM balances_with_gap_days b
   INNER JOIN days d ON b.day <= d.day
   AND d.day < b.next_day -- 最初のトランスファー後、1日ごとの結果を得る
 --INNER JOIN erc20.tokens erc ON b.token_address = erc.contract_address

   GROUP BY 1,
            2
   ORDER BY 1,
            2 )

SELECT 
       b.day,
       sum(balance)/10^18 as balance　--decimal が18でないトークンはここを書き換える必要がある
       
FROM balance_all_days b
WHERE balance > 0  and address=CONCAT('\x', substring('{{Target Address}}'
                                                      FROM 3))::bytea -- ここに自分のアドレスを入力{{TargetAddress}}
                                                      
GROUP BY 1
ORDER BY 1 ;

変えたのはb.day 単位で抽出しただけですね。

SELECT 
       b.day, -- これで日付単位のデータを取得できる
       sum(balance)/10^18 as balance　--decimal が18でないトークンはここを書き換える必要がある
       
FROM balance_all_days b
WHERE balance > 0  and address=CONCAT('\x', substring('{{Target Address}}'
                                                      FROM 3))::bytea -- ここに自分のアドレスを入力{{TargetAddress}}

では良きDune Analyticsライフを。