おはようございます。
web3リサーチャーのmitsuiです。

本日は昨日に引き続き「Dune Japan」による寄稿記事です。web3 Research JAPANはDune Japanと連携し、今後オンチェーン分析に関する記事を定期的に発信していきます。まずは「Duneに関する基本的な使い方」を発信し、今後は月1~2本を目安にオンチェーン分析に関する記事を更新予定です。ぜひ楽しみにお待ちください！

ゼロからわかるDuneを使ったオンチェーン分析の始め方【基礎編】に続き、今回は実践編として、Duneを使った実際のオンチェーン分析を一通りの流れを書いていきます。

まだ、基礎編を読んでいない方は、下記から基礎編をご一読ください。

分析内容の選定
分析のための指標・項目の洗い出し
SQLクエリの作成と分析
実践編のまとめ

分析内容の選定

まず、今回のオンチェーン分析で取り扱う分析内容を決めていきます。

最近、日本国内でもSonyがOptimismのSuperchainを活用したLayer2チェーン（以下、L2チェーン）「Sonium」を展開すると発表したことが国内でも話題となりました。

ただ、SuperchainのようにL2チェーンを比較的簡単に立ち上げられるようになったこともありますので、L2チェーン自体が乱立している状態になっています。

仮に、私がDeFiプロジェクトを立ち上げようとしているFounderだった時に、どのL2チェーンに展開するべきだろうか、という疑問が出てきます。DeFIにせよNFTプロジェクトにおいても、どのチェーンに展開するかというのは非常に重要な意思決定になります。

自分たちのプロジェクトはどのチェーンがマッチしていて、どこが最もアツいのかということ検討した上で、意思決定をする必要があります。ここで、オンチェーン分析が必要になります。

本分析では、Duneを使ってオンチェーンデータから「最も盛り上がっているL2はどこか」ということを明らかにしていきます。

分析のための指標・項目の洗い出し

まず、「最も盛り上がっているL2はどこか」ということをもっと細かく言語化する必要があります。盛り上がっている状態を以下のような項目から考えることができます。もちろん他にも指標はあります。

• アクティブユーザーが多い

• トランザクション数が多い

• DEXでの取引量

また、これらの指標をどの程度の時間軸でみる必要があるのかも重要です。1年間というスパンでデータをみる必要がある可能性もあれば、半年あるいは30日間でデータをみる必要がある可能性もあります。今回は直近30日間で見ていきます。

上記の指標から「最も盛り上がっているL2はどこか」というのを総合的に判断します。

L2チェーンが山のようにあるので、今回の対象とするチェーンを以下の5つに絞りました。

• Arbitrum

• Optimism

• Base

• Polygon zkEVM

• zksync

今回最終的に制作したダッシュボードは下記のリンクになります。

🔗https://dune.com/gussan_0214/dune-japan-studycase-dashboard

SQLクエリの作成と分析

検証項目：アクティブユーザー

アクティブユーザーが多いかどうかは、トランザクションを起こしたアドレスの推移をみていきます。

例えば、Optimismに関するアクティブアドレスの日次推移を抽出するには下記のようなコードになります。

SELECT
    DATE_TRUNC('day', block_time) AS date,
    count(DISTINCT "from") as active_address, -- DISTINCTは、　重複なし
    'optimism' as blockchain -- どのチェーンの日次データなのかを区別するため
FROM optimism.transactions
WHERE block_time >= NOW() - INTERVAL '30' day ---現在から30日前以降のデータをフィルター
GROUP BY date -- どのカラムごとで集計するかを記載する。列の番号でもいいためdateを1と記述してもOK

さらに他のチェーンも比較してみるために、下記のように統合します。

SELECT
    DATE_TRUNC('day', block_time) AS date,
    count(DISTINCT "from") as active_address, 
    'optimism' as blockchain 
FROM optimism.transactions
WHERE block_time >= NOW() - INTERVAL '30' day
GROUP BY 1

UNION -- 同じカラムで表示するために、カラム名を合わせる

SELECT
    DATE_TRUNC('day', block_time) AS date,
    count(DISTINCT "from") as active_address, 
    'base' as blockchain 
FROM base.transactions
WHERE block_time >= NOW() - INTERVAL '30' day
GROUP BY 1

UNION...（以下省略）

ちなみに、今回は30日というスパンでやっていますが、ユーザー側でパラメータとして設定する場合、「Add parameter」を選択し、パラメータ名を設定し、（今回は、span_parameterと設定）初期値を設定した上でクエリを下記のような記述になります。

SELECT
    DATE_TRUNC('day', block_time) AS date,
    count(DISTINCT "from") as active_address, 
    'optimism' as blockchain 
FROM optimism.transactions
WHERE block_time >= NOW() - INTERVAL '{{span_parameter}}' day 
GROUP BY date 

結果的に、下記のような結果が出力されました。

このテーブルデータではかなり分かりにくいので線グラフでビジュアライズしてみましょう。

X軸にdate、y軸にactive_address、Group byにblockchainと設定します。

ここ30日間では、常にBaseが上位にあることがわかりました。また、Base以外は横ばいあるいは若干右肩下がりにですが、Baseは比較的右肩に上がっているように見えます。

このアクティブアドレスの観点ではBaseが盛り上がっているように見えます。

また、抽出した対象の値に大きな幅がある場合、今回のように潰れてしまいます。

Y軸のオプションのLogarithmicを選択すると、対数グラフで表現できるようになり、全体感を把握することができます。逆に、対数グラフの場合はメモリが大きくなると変化量が捉えづらくなることも欠点となるので、うまく使い分けをしてください。

検証項目：トランザクション数

トランザクション数に関しては、先ほどのアドレスの部分がhash（各トランザクションで発行される一意の値）になっていれば、似たような記述で進めることができます。

また、先ほどのアドレスに関する日次推移と一緒に比較するために追記する形でクエリを書いていくと下記のようになります。

SELECT
  DATE_TRUNC('day', block_time) AS date,
  count(DISTINCT "from") as active_address,
  count(hash) as total_tx, --追記部分
  'optimism' as blockchain 
FROM optimism.transactions
WHERE block_time >= NOW() - INTERVAL '{{span_parameter}}' day
group by 1

UNION ...（以下省略）

こちらを実行すると、下記のような結果が得られます。

こちらも先ほど同様、ビジュアライズしてみましょう。

base、arbitrum、optimsm、zksync、polygon zkevmという並びで先ほどのアドレスの並び順とそこまで大きく変わっていない印象です。

盛り上がりとは少し別の話で、baseはoptimsmのsuperchainを使っているわけですが、チェーンとしてのoptimismはそこまでトラクションがついていないという結果が得られています。

また、アドレスとトランザクション数の２つをみてきましたが、別の観点として、ユーザーの熱狂度を考えると、1アドレスあたりの実行回数というのも気になります。実測することもできますが、今回は概算で考えると、日毎のtotal_tx / active_addressという計算をすれば良さそうです。

コードは下記のようになります。細かい変化を追うためにactive_address、total_txをdoubleというデータ型に変換してactive_addressをtotal_txで割っています。

SELECT
	DATE_TRUNC('day', block_time) AS date,
	count(DISTINCT "from") as active_address,
	count(hash) as total_tx, --追記部分
	CAST(count(DISTINCT "from") as double)/ CAST(count(hash) as double) as tx_count_per_address -- cast(データ as データ型)で変換
	'optimism' as blockchain 
FROM optimism.transactions
WHERE block_time >= NOW() - INTERVAL '{{span_parameter}}' day
group by 1

UNION ...（以下省略）

カラム名をasで変更していますが同じテーブルでは反映されないので、整形した変数名が適用できません。なので、サブクエリを使って以下のように整理をしてみました。

WITH l2_blockchain_union AS(
    SELECT
		  DATE_TRUNC('day', block_time) AS date,
		  count(DISTINCT "from") as active_address,
		  count(hash) as total_tx, --追記部分
		  'optimism' as blockchain 
		FROM optimism.transactions
		WHERE block_time >= NOW() - INTERVAL '{{span_parameter}}' day
		group by 1
		
		UNION ...（以下省略）
)
		
SELECT 
	*, 
	CAST(total_tx as double)/ CAST(active_address as double) as tx_count_per_address
from l2_blockchain_union

そして、以下のようなテーブルデータが得られました。

これをビジュアライズしてみると以下のようになります。

なんとこの指標では、Optimismが群を抜いて高く、先ほどのbaseはかなり下位群になります。

ただ、これは総トランザクションを総アドレスで割っただけなので、データの偏りがある可能性があります。今回はそこまで正確な数値を出すことが目的ではありませんが、より精緻な分析をするのであれば、アドレスごとにトランザクションのカウントを行い、トランザクション数の分布を行った方がいいかもしれません。

このように、各データにどのような操作をすることで期待される結果が出力されるかというのをしっかりと組み立てて分析をする必要があります。

今回作成したクエリ：https://dune.com/queries/4083785/6876876/

検証項目：DEXでの取引量

次にDEXでの取引量です。DEXの取引量についてはdex.tradesというDuneがキュレーションしたデータセットがあります。基本的には先ほどの記法とほとんど同じで、今回は複数のチェーンをまとめ上げたデータセットなので、blockchainというカラムから対象のチェーンをフィルタリングします。

DEXでの取引量にくわえて先ほどのようにユーザー数やトランザクション数も合わせて出してみました。コードは下記のようになります。

select 
	block_date as date,
	blockchain,
	count(tx_hash) as dex_tx_count,
	count(distinct tx_from) as dex_active_address,
	sum(amount_usd) as dex_total_usd
from dex.trades
where blockchain IN ('arbitrum', 'optimism', 'zksync', 'zkevm','base')
and block_time >= NOW() - INTERVAL '30' day
group by 1, 2

出力された結果がこちらです。

先ほどまでと同様ビジュアライズしていきましょう。

アドレス数の推移は、Baseが群を抜いています。先述した通りこのような場合は、Logarithmicを利用して、他のチェーンの序列も把握しましょう。

こちらが対数グラフ化したもので、Baseに次いでArbitrumが大きいですが、直近1週間近くではBaseのユーザー数はArbitrumのユーザー数の40倍程度あります。

DEXを利用しているトランザクション数の推移ですが、やはりBaseが圧倒的にトランザクション数が多いです。ユーザー数にかなりの差がある一方で、トランザクション数で見るとユーザー数ほどの差はないのが直感的にわかるため、Arbitrumは1ユーザーのDEX利用数が多いと仮説が立てられます。

さらにDEXでの取引額を確認すると、BaseとArbitrumの取引額は同程度であり、先ほどのArbitrumの1ユーザーあたりのトランザクション数と合わせて考えると、Arbitrumの1ユーザーあたりの運用額が大きくDeFiに強みを持っているL2といえるかもしれません。

今回作成したクエリ：https://dune.com/queries/4084148/6877250/

分析の結果

各チェーンごとのアクティブユーザー数、トランザクション数やDEXの利用状況を可視化してみました。どのチェーンが最も盛り上がっているのかというのを一概にいうことはできませんが、単純なチェーンのトランザクション数やアクティブ数の多さではBaseがかなり圧倒していました。

一方でDeFiの面では、運用規模でいうとArbitrumも盛り上がりがありそうです。

筆者としてもBaseには、DeFiよりもSocialを扱っているイメージがあり、ArbitrumではDeFiを多く扱っているイメージがあるため、この結果は両者のチェーンの特性を捉えた結果とも言えます。

冒頭にの自分がDeFiプロジェクトのファウンダーだった時にどこのチェーンに展開するかという話に戻りますが、この分析は意思決定の要素でしかありません。

DeFiの運用額で言えば、Arbitrumの上かもしれませんが、このユーザー数の少なさに対しての運用額の大きさはリスクとも考えられます。

なので、逆にユーザーが多いけど、DeFiがそこまで発展していないzkSyncに展開するかもしれないし、はたまた全然別のチェーンかもしれない。

といった感じで、得られた結果をもとに意思決定を行います。

実践編のまとめ

ここまで、分析指標の洗い出しからクエリの作成、SQLの記述、ビジュアライズ、分析までの一連の流れを行いました。

今回はシンプルな集計を中心に行い、オンチェーン分析をどのような流れで行うのかを把握していただけたかと思うので、より高度な分析や精緻な分析についてはぜひ挑戦してみてください。

ここまで読んでいただきありがとうございました。

↓基礎編はこちら

«関連 / おすすめリサーチ»

Contributor：Dune Japan

オンチェーン分析ツール DuneAnalyticsの活用を支援し、日本国内のオンチェーン分析を活発化することを目的とした日本コミュニティ。NFTの社会実装に向けた基盤を構築するシンシズモ株式会社が運営。（執筆：https://x.com/0xguss3）

10月11日(金)にDune Japan初のイベント「Dune Japan meetup Vol.1」が開催されます。本イベントを通じて、初心者から上級者までが交流し、オンチェーンデータの可能性を共に探求できる場を提供していきます。

☝️詳細確認、参加申し込みはこちら

免責事項：リサーチした情報を精査して書いていますが、個人運営&ソースが英語の部分も多いので、意訳したり、一部誤った情報がある場合があります。ご了承ください。また、記事中にDapps、NFT、トークンを紹介することがありますが、勧誘目的は一切ありません。全て自己責任で購入、ご利用ください。

About us：🇯🇵🇺🇸🇰🇷🇨🇳🇪🇸の5ヶ国語で展開されるweb3ニュースレターの日本語版。「1日5分でweb3をより深く学ぶ」をコンセプトに、web3の注目トレンドやプロジェクトの解説、最新ニュース紹介などのリサーチ記事を毎日配信しています。

Author：mitsui @web3リサーチャー
「web3 Research」を運営し、web3リサーチャーとして活動。

Contact：法人向けのリサーチコンテンツの納品や共同制作、リサーチ力を武器にしたweb3コンサルティングや研修なども受付中です。詳しくは以下の窓口よりお気軽にお問い合わせください。（📩 X / HP）