Kyoto Angelsのオンチェーン分析【Dune】

はじめに

完全な初学者であるため間違いを含んでいるかと思います。ご指摘していただけると嬉しいです。

　以前からDuneの存在は知っていましたが、SQLが書けるわけでは無かったため特に触ってみようとは考えていませんでした。
　しかし今どんなNFTプロジェクト、dApp、web3サービスが伸びているのかをリサーチすることとなりweb3版data.ai(旧App Annie)としてDuneが活用できるのではと思い、SQLの勉強も兼ねて触ってみることにしました。以下が成果物です。
https://dune.com/chaichai/kyoto-angels
＊YoshitakaOkayamaさんのnoteがとても分かりやすく使い方を解説されていて参考にさせていただきました。
https://note.com/0xyoshi/n/n82ce78f34187

Duneとは？

Dune(旧Dune Analytics)とはオンチェーン上のデータをSQL文によって取得・分析できるプラットフォームです。
https://fungibleanalyst.com/howtouse-duneanalytics/

Kyoto Angelsとは？

DuneでいきなりdAppsの研究を始めるより、まずはSQLの勉強も兼ねて自分の好きな題材を選ぶことにしました。Kyoto AngelsはOpen Seaで展開されているNFTコレクションで、私が最初に購入しweb3への門を開けてた思い出のNFTです。
https://www.kyotoangels.com/
Duneでは他の方が作ったクエリ、ダッシュボードを閲覧・フォークすることができます。調べてみるとKyoto Angelsはまだ作られていませんでした。

目標物

SQL初学者であるため、他の分析ダッシュボードのようにリッチなものは作ることが出来ません。まずは簡単に作れそうなものから作っていくことにしました。最終的には以下の５つを一旦作成しました。

1. mintによる売上高
2. 発行されたNFT数
3. 日々のトラクション(MATIC建)
4. 日々の新規ホルダー数
5. 累計保有経験者

実装

1.mintによる売上高

select sum(cast(value as double) / 1e18)
from polygon.traces
-- 以下のアドレスはコレクションを展開しているUwUMistLabsのものです。
where "to" = 0x9056a967957d65E3070D59988C0D8A671C5B72Aa

　元々polygon.transactionsからトランザクション履歴を取得し合計を算出していましたが、明らかにOpenSeaのコレクションページから参照できる取引高の値(12ETH)と乖離していました。そのためtracesのvalueカラムを代用として参照しました。
　使ってみると実行結果が12ETHを超えていたため、NFT取引以外の値も考慮してしまっていますので改良の余地が多分にありそうです。
(erc721_polygon.evt_Transferから参照して行ったほうが正確な値が出てきそうです。)

2.発行されたNFT数

select count(*) as suppliedNFT
from erc721_polygon.evt_Transfer
where 
　　 -- UwUMistLabsのアドレス
    "to" = 0x9056a967957d65E3070D59988C0D8A671C5B72Aa and
    -- null address
    "from" = 0x0000000000000000000000000000000000000000

Ethereumでは、すべてのNFTはnullアドレスから発行されます。
https://note.com/0xyoshi/n/n82ce78f34187より

Polygonでも同様にNFTはnullアドレスから発行されます。
実行結果(10,004)とOpenSeaに展開されている作品数(10,091)は非常に近いことからある程度指標としての正確性は高いかと思います。

3.日々のトラクション(MATIC建)

SELECT cast(value as double) / 1e18 AS matic, date_trunc('day', block_time) AS day
FROM polygon.traces
WHERE "to" = 0x9056a967957d65E3070D59988C0D8A671C5B72Aa
ORDER BY block_time

こちらもpolygon.transactionsの代わりにtracesを使っています。

4.日々の新規ホルダー数

SELECT 
  -- 重複の無いようにNFTが送られたアドレス数を取得
  COUNT(DISTINCT to) AS number_of_owners,
  DATE(evt_block_time) AS transaction_date
FROM erc721_polygon.evt_Transfer
-- Kyoto Angelsのコントラクトアドレス
WHERE "contract_address" = 0x67eC850B2Cb4f9a171487cAFc48e3F8d964e12c6
-- 日付毎にまとめる
GROUP BY DATE(evt_block_time)
-- 古い順から並び替え
ORDER BY DATE(evt_block_time)

新しくNFTを購入したアドレス数を日付毎に算出しています。

5.累計保有経験者

WITH cumulative_owners AS (
  SELECT 
    DATE(evt_block_time) AS transaction_date,
    COUNT(DISTINCT to) AS daily_owners
  FROM erc721_polygon.evt_Transfer
  WHERE "contract_address" = 0x67eC850B2Cb4f9a171487cAFc48e3F8d964e12c6
  GROUP BY DATE(evt_block_time)
),
running_total AS (
  SELECT 
    transaction_date,
    daily_owners,
    SUM(daily_owners) OVER (ORDER BY transaction_date) AS cumulative_owners
  FROM cumulative_owners
)
SELECT 
  transaction_date,
  cumulative_owners
FROM running_total

　当初は各時点での保有者数の推移をグラフとして算出したいと考えていました。
しかし4のコードを使用すると、購入者=保有者では無いため現在までの購入者(保有履歴のある人)の累積を出すこととしました。
　実際現在の保有者が663人である一方で実行結果は1/31時点で958人となっています。回転率はそこまで高くない印象です。

まとめ

　簡単なSQLの基礎を勉強できたとともにDuneの凄さを実感しました。web3プロダクトが増えていく中では自社でデータを分析し、競合のデータを正確にベンチマークできることでこれからの企業戦略も変わってくる気がしています。