【Flipside Crypto】オンチェーン分析ツールを使ったEthereumのデータ分析方法を徹底解説

本記事では、オンチェーン分析プラットフォームである”Flipside Crypto”を使用し、Ethereumのブロックチェーンに記録されたトランザクションデータの分析方法について解説していこうと思います。

本記事を読むことで以下について知ることができます。

  • Flipside Cryptoとは何か
  • Flipside Cryptoの使い方
  • オンチェーン分析の始め方

それでは早速解説していきましょう!

Flipside Cryptoとは?

QuarkChain On Marketing And the Art of Storytelling - On the Flipside
https://flipsidecrypto.xyz/

Flipside Cryptoはブロックチェーン分析プラットフォームの一つであり、様々な暗号プロジェクトが持つ課題をデータ分析によって明らかにし、分析によって得られたインサイトをコミュニティー内で共有することを目指しています。分析者はFlipside Cryptoのプラットフォーム上で提示される課題に対して、SQLクエリを使用したデータ分析とダッシュボード作成を行い、バウンティ(懸賞金)を得ることができます。分析者はバウンティ獲得のために質の高いデータ分析を行い、各暗号プロジェクトは成長のためのインサイトを迅速に得ることができる双方にメリットのある仕組みとなっています。

Flipside Cryptoでは常時10個程度のバウンティプロジェクトがオープンとなっており、バウンティ獲得に向けて誰でもいつでも参加可能です。課題の難易度に合わせてBeginner(初級)・ Intermediate(中級)・ Advances(上級)の3段階に区分されているため、まずはBeginnerの課題から取り組むといいでしょう。

Flipside Cryptoの詳細はこちらの記事で詳しく解説しています。ぜひご覧ください!

まずはチュートリアルからオンチェーン分析を始めよう

Flipside Cryptoでは誰もがいつからでもバウンティの課題に取り組むことができますが、分析ツールの使用に慣れてない人にとってはBeginnerレベルの課題でさえも取り組むハードルが高いでしょう。そこで、Flipside Cryptoには分析チュートリアルが用意されており、初めてオンチェーン分析に取り組む人はこちらから取り組んでみてはいかがでしょうか。

以下のリンクからチュートリアルの内容を確認することができます。

ここからはチュートリアルの内容に沿って、オンチェーン分析の方法について解説していこうと思います!

0. 課題を確認する

まずは本チュートリアルの課題を確認します。

課題

Visualize the amount of USDC swapped and count of swaps in the USDC-WETH SushiSwap pool by day in the past 7 days using the fact_event_logs table. In this walk through, we are going to analyze emitted swap events from the SushiSwap USDC-WETH pool.


fact_event_logs テーブルを使用して、過去 7 日間の USDC-WETH SushiSwap プールの日別スワップ量とスワップ回数を可視化します。このウォークスルーでは、SushiSwap USDC-WETH プールから発行されたスワップ・イベントを分析します。

課題の要点は、「過去7日間におけるSushiswapのUSDC-WETHプールでの日毎のスワップ量とスワップ回数を可視化する」ということであり、データの抽出にはETHEREUM_CORE内のFACT_EVENT_LOGSテーブルが使用できるようです。

はじめに各単語の意味を理解しておきましょう。

Susiswapとは

Susiswapは2020年にサービスを開始した暗号資産を取引できる分散型取引所(DEX)です。分散型取引所は管理者がいなくとも運営されているという特徴があり、コインチェックやGMOコインといった1つの企業が管理する取引所とは異なります。SusiswapはSUSHIという独自トークンを発行しています。

USDC-WETCプールとは

USDCは米ドルを担保に発行された暗号資産であり、WETHはイーサリアムを他の暗号資産と交換することができるように発行された暗号資産です。USDC-WETCプールは、USDCとWETCが預け入れられている場所のようなものです。一般的に預金額が多いと流動性が高く、売り手と買い手が多く存在するため安定した取引ができると言われています。

スワップとは

スワップはある暗号資産を他の暗号資産へ交換することです。今回のチュートリアルではUSDCとWETCの日毎の交換に注目します。

1. SushiSwapのUSDC-WETHプールのスワップイベントを抽出する

はじめに、SushiSwapの取引データから対象データを抽出しましょう。
SushiSwapのUSDC-WETCプールのアドレスは0x397FF1542f962076d0BFE58eA045FfA2d347ACa0です。

SELECT
   *
FROM
   ETHEREUM_CORE.FACT_EVENT_LOGS
WHERE
   block_timestamp >= CURRENT_DATE - 6
   AND contract_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')  -- this is the USDC-WETH SushiSwap Pool Address
   AND event_name IN ('Swap')

ここでは3つの条件をもとにデータのフィルタリングを行ないます。

  • block_timestamp:トランザクションの発生時刻であり、過去7日間のデータを指定します。
  • contract_address:取引時のアドレスであり、SushiSwapのUSDC-WETHプールのアドレスを指定します。
  • event_nameトランザクションのイベントタイプであり、スワップを指定しています。

出力結果を見ると、EVENT_INPUTSのカラムに以下のようなJSON形式のデータが格納されています。

{
"amount0In":"0",
"amount0Out":"108427587",
"amount1In":"82794933823612249",
"amount1Out":"0",
"sender":"0xdef1c0ded9bec7f1a1670819833240f027b25eff",
"to":"0x31ce3808d5789c90b942b70a0599ad4992595cae"
}

2. JSON形式のデータを処理する

次に、EVENT_INPUTSカラム内のJSON形式のデータを処理する方法について解説します。

JSON形式のデータは以下の式を使用して処理することができます。

<COLUMN_NAME>:<FIELD_NAME>::<FIELD_FORMAT> AS <FIELD_NAME>

SELECT
   block_number,
   block_timestamp,
   tx_hash,
   event_index,
   contract_address,
   event_name,
   event_inputs,
   event_inputs :amount0In :: INTEGER AS amount0In,
   event_inputs :amount0Out :: INTEGER AS amount0Out,
   event_inputs :amount1In :: INTEGER AS amount1In,
   event_inputs :amount1Out :: INTEGER AS amount1Out,
   event_inputs :sender :: STRING AS sender,
   event_inputs :to :: STRING AS to_address
FROM
   ETHEREUM_CORE.FACT_EVENT_LOGS
WHERE
   block_timestamp >= CURRENT_DATE - 6
   AND event_name = ('Swap')
   AND contract_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')

amount0とamount1が取引量を表しています。
現段階ではamount0とamount1のどちらがUSDCとWETHに対応するのかは不明です。

3. トークン詳細情報を収集する

amout0とamount1に対応するトークンを調べる

amount0とamount1のどちらがUSDCとWETHに対応するかを調べたいと思います。 
DIM_DEX_LIQUIDITY_POOLSテーブルで、流動性プールに関する詳細情報を調べることができます。

SELECT
   pool_name, pool_address, token0, token1
FROM
   ETHEREUM_CORE.DIM_DEX_LIQUIDITY_POOLS
WHERE
   pool_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')

出力結果

スクロールできます
POOL_NAMEPOOL_ADDRESSTOKEN0TOKEN1
USDC-WETH SLP0x397ff1542f962076d0bfe58ea045ffa2d347aca00xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb480xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2

各アドレスから
token0がUSDC(=0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48)、
token01がWETH(=0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2)であることがわかりました。

各トークンの小数点桁数を調べる

次に、DIM_CONTRACTSテーブルで各トークンの小数点の桁数を調べたいと思います。

-- SushiswapのUSDC-WETHプールのメタデータを取得する
WITH pools AS (
  SELECT
  	pool_name, pool_address, token0, token1
  FROM
  	ETHEREUM_CORE.DIM_DEX_LIQUIDITY_POOLS
  WHERE
  	pool_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')
),
-- 各トークンの小数点桁数を取得する
SELECT
	address, symbol, decimals
FROM
	ETHEREUM_CORE.DIM_CONTRACTS
WHERE
	address = (SELECT  LOWER(token1) FROM pools)
  OR address = (SELECT LOWER(token0) FROM pools)

出力結果

スクロールできます
ADDRESSSYMBOLDECIMALS
0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2WETH18
0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48USDC6

以上からUSDCが18桁、WETHが6桁目に小数点が付くことがわかりました。

4. ベースのテーブルを作成する

ここまでの分析で、JSONデータの処理、amount0・amount1とUSDC・WETHの対応関係の特定、各トークンの小数点の桁数について理解できたため、データを集計して分析結果を可視化しましょう。

なお、小数点の桁数の指定は以下の式を使用します。

<AMOUNT_COLUMN> / POW(10,DECIMALS)

-- SushiswapのUSDC-WETHプールのメタデータを取得する
WITH pools AS (
  SELECT
  	pool_name, pool_address, token0, token1
  FROM
  	ETHEREUM_CORE.DIM_DEX_LIQUIDITY_POOLS
  WHERE
  	pool_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')
),
 -- USDC-WETHプールにおける各トークンの小数点桁数を取得する
decimals AS (
  SELECT
  	address, symbol, decimals
  FROM
  	ETHEREUM_CORE.DIM_CONTRACTS
  WHERE
  	address = (SELECT  LOWER(token1) FROM pools)
    OR address = (SELECT LOWER(token0) FROM pools)
),
-- 各トークンの小数点桁数を結合する
pool_token_details AS (
  SELECT
  	pool_name, pool_address, token0, token1,
    token0.symbol AS token0symbol,
    token1.symbol AS token1symbol,
    token0.decimals AS token0decimals,
    token1.decimals AS token1decimals
  FROM
  	pools
  LEFT JOIN decimals AS token0
  ON token0.address = token0
  LEFT JOIN decimals AS token1
  ON token1.address = token1
),
-- 過去7日間のスワップに関するトランザクションデータを取得する
swaps AS (
  SELECT
    block_number, block_timestamp, tx_hash, event_index, contract_address, event_name, event_inputs,
    event_inputs :amount0In :: INTEGER AS amount0In,
    event_inputs :amount0Out :: INTEGER AS amount0Out,
    event_inputs :amount1In :: INTEGER AS amount1In,
    event_inputs :amount1Out :: INTEGER AS amount1Out,
    event_inputs :sender :: STRING AS sender,
    event_inputs :to :: STRING AS to_address
  FROM
  	ETHEREUM_CORE.FACT_EVENT_LOGS
  WHERE
    block_timestamp >= CURRENT_DATE - 6
    AND event_name = ('Swap')
    AND contract_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')
),
-- トランザクションデータにメタデータを結合する
swaps_contract_details AS (
  SELECT
    block_number,
    block_timestamp,
    tx_hash,
    event_index,
    contract_address,
    amount0In,
    amount0Out,
    amount1In,
    amount1Out,
    sender,
    to_address,
    pool_name,
    pool_address,
    token0,
    token1,
    token0symbol,
    token1symbol,
    token0decimals,
    token1decimals
  FROM
    swaps
    LEFT JOIN pool_token_details
    ON contract_address = pool_address
),
-- 各トークンの小数点を指定した桁数に変換する
final_details AS (
  SELECT
    pool_name,
    pool_address,
    block_number,
    block_timestamp,
    tx_hash,
    amount0In / pow(10, token0decimals) AS amount0In_ADJ,
    amount0Out / pow(10, token0decimals) AS amount0Out_ADJ,
    amount1In / pow(10, token1decimals) AS amount1In_ADJ,
    amount1Out / pow(10, token1decimals) AS amount1Out_ADJ,
    token0symbol,
    token1symbol
  FROM
  	swaps_contract_details
)
-- 以下でデータを集約する
SELECT * FROM final_details;

5. データを集約する

ここまででデータを集約するためのベースとなるテーブルの作成ができました。

今回は、「USDC-WETHプールでスワップされた取引量とスワップ回数を日毎に集計すること」を目指しています。作成したテーブルを使用してデータを集約していきましょう。

取引量

スワップによって増加したUSDC(USDC_IN)と減少したUSDC(USDC_OUT)を算出します。また、それぞれの合計を求めることで最終的にスワップされたUSDCの増減(TOTAL_USDC_VOL)を確認できます。

sum(amount0In_ADJ) as USDC_IN
sum(amount0Out_ADJ) as USDC_OUT
sum(amount0In_ADJ) + sum(amount0Out_ADJ) as TOTAL_USDC_VOL

取引回数

取引回数を確認するには、トランザクションハッシュ(tx_hash)をカウントします。

count(tx_hash) as swap_count

日毎の集計

データを日毎に集計するには、date_trunc() 関数でblock_timestampを1日単位に変更します。

date_trunc('day', block_timestamp) as DATE

まとめると、以下のクエリを実行することでデータの集約が可能となります。

SELECT
   DATE_TRUNC('day', block_timestamp) AS DATE,
   COUNT(tx_hash) AS swap_count,
   SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS usdc_vol
FROM
   final_details
GROUP BY DATE
ORDER BY DATE DESC

6. 可視化する

最後にグラフを作成します。

FlipsideCryptoでは抽出したデータから簡単にグラフ作成が可能です。
今回はX軸に日付、Y軸に取引量と取引回数を指定しています。以下がその結果です。

ここまでの分析の内容は以下のダッシュボードに記載してありますので、参考にしてみてください。

まとめ

今回はFlipsideCryptoの分析プラットフォームを使用しながら、こちらのチュートリアルの内容を解説しました。課題理解、クエリ作成、グラフ作成を実施することでオンチェーン分析の全体フローを掴んでいただくことができたのではないでしょうか。

FlipsideCryptoには懸賞金が獲得できるバウンティプロジェクトがあります。ぜひ自身のスキルアップや実力試しとして、チャレンジしてみてはいかがでしょうか。

本メディアでは継続的にFlipsideCryptoなどのオンチェーン分析ツールやweb3に関するデータ分析の情報を発信していきたいと思います。

シェアでさらに記憶に定着
  • URLをコピーしました!

コメント

コメントする