【Flipside Crypto】オンチェーン分析でDEX(分散型取引所)の取引量を可視化する

2022 12/21

2022年12月21日

本記事では、Flipside Cryptoのバウンティプログラムの課題を題材として、DEX（分散型取引所）のスワップ量に関するオンチェーン分析の方法を解説します。Flipside Cryptoのプラットフォーム上でバウンティプログラムの課題提出までの方法についても併せて解説していこうと思います。

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

バウンティプログラムとは何か
バウンティプログラムの解答の作成方法
バウンティプログラムの解答の提出方法

Flipside Cryptoとは何かについては以下の記事で詳細を解説していますので合わせてご覧ください。

あわせて読みたい

ブロックチェーン分析で稼ぐ？Flipside Cryptoで暗号資産を獲得しようこの記事ではブロックチェーンデータの分析プラットフォームの1つであるFlipside Cryptoの概要と使い方について紹介します。その他の分析プラットフォームについては以…

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

バウンティプログラムとは何か

オンチェーン分析プラットフォームであるFlipside Cryptoの最大の特徴は、分析によってバウンティ（懸賞金）を得られることです。各ブロックチェーンプロジェクトは大量のトランザクションの中から洞察を得るために、Flipside Cryptoのプラットフォーム上に懸賞金つきの課題を提示し、分析者はその課題に対してFlipside Cryptoの分析ツールを使用しながら解答を作成します。優秀なダッシュボードを作成した分析者に暗号資産による懸賞金が付与される仕組みとなっています。

バウンティプログラムを探す

毎週コミュニティメンバーから質問を募集しており、懸賞金付きのバウンティプログラムが作成されます。質問の回答のために優秀なダッシュボードで作成したアナリストには報奨金が与えられます。

課題に回答する

すべての課題には、Flipside Cryptoの無料のオンチェーンデータセットを使用して解決する必要があります。Flipsideの無料オンチェーンデータにアクセスし、分析結果をダッシュボードでまとめます。

回答結果を提出する

ダッシュボードが完成したら、プロジェクトページにあるフォームを使用して送信してください。過去の懸賞金が付与されたダッシュボードを参考にすることもできます。

暗号資産を稼ぐ

ダッシュボードが期限内に提出され基準を満たしていれば、暗号資産で報酬が支払われます。質問に対するダッシュボードの最優秀賞とそれに付随する特別報酬を手にすることもできます。

バウンティプログラムの解答の作成方法

ここからはバウンティププログラムの質問に解答するために、ダッシュボードを作成してみましょう。今回対象とする質問は「Ethereum Educational Bounties: Ethereum_Core Tables Walkthrough」というタイトルの課題になります。こちらは誰でも参加可能な初級者用の常設プログラムです。そのため、懸賞金はついていませんが、中級や上級レベルの質問に回答していくために多くのことを学ぶことができるものになります。

以下のリンクから確認できます。

あわせて読みたい

Flipside Data App On-chain data available anytime, anywhere, for free.

0. 課題を確認する

まずは課題を確認します。

課題

By using the ethereum_core tables, create the analysis to visualize the amount of WBTC swapped and count of swaps for WBTC-WETH pool on Sushiswap from April 1 – April 15, 2022

– the WBTC-WETH pool on Sushiswap’s contract address is 0xceff51756c56ceffca006cd410b03ffc46dd3a58

– Does this pool have more or less swap volume as compared to WETH-USDC?

ethereum_core テーブルを使用して、2022/04/01-04/15間においてSushiSwapでのWBTC-WETH プールの日別スワップ量とスワップ回数を可視化する分析を実施します。

SushiSwapでのWBTC-WETH プールのアドレスは0xceff51756c56ceffca006cd410b03ffc46dd3a58
WBTC-WETHプールのスワップ量とスワップ回数はWETH-USDCプールと比較すると多いか少ないか

課題の要点は、「2022/04/01-04/15におけるSushiswapのWBTC-WETHプールとWETH-USDCプールの日毎のスワップ量とスワップ回数を可視化し、どちらが多いかを明らかにする」ということであり、データの抽出にはETHEREUM_CORE内テーブルが使用できるようです。

WETH-USDCプールにおける分析は以下の記事で詳細を解説しています。Flipside Cryptoで初めてオンチェーン分析を始める人は、まずはこちらの記事から読むことをお勧めします。

あわせて読みたい

【Flipside Crypto】オンチェーン分析ツールを使ったEthereumのデータ分析方法を徹底解説本記事では、オンチェーン分析プラットフォームである”Flipside Crypto”を使用し、Ethereumのブロックチェーンに記録されたトランザクションデータの分析方法について解…

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

SushiSwapの取引データから対象データを抽出する方法を解説します。

SELECT
   *
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 BETWEEN '2022-04-01 00:00:00' AND '2022-04-15 23:59:59'
   AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')  -- the WBTC-WETH pool on Sushiswap’s contract address
   AND event_name IN ('Swap')
;

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

block_timestamp：トランザクションの発生時刻であり、2022/04/01-04/15の期間のデータを指定します。
contract_address：取引時のアドレスであり、SushiSwapのWBTC-WETHプールのアドレスを指定します。
event_name：トランザクションのイベントタイプであり、スワップを指定しています。

EVENT_INPUTSカラム内はJSON形式でデータが格納されているため、以下の式を使用して処理します。

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

現段階ではamount0とamount1のどちらがWBTCとWETHに対応するのかは不明であるため、調査が必要です。

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

amount0とamount1のどちらがWBTCとWETHに対応するかを調べたいと思います。　

DIM_DEX_LIQUIDITY_POOLSテーブルで、流動性プールに関する詳細情報を調べることができます。また、DIM_CONTRACTSテーブルで各トークンの小数点の桁数も調べておきます。

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

出力結果

スクロールできます

ADDRESS	SYMBOL	DECIMALS
0x2260fac5e5542a773aa44fbcfedf7c193bc2c599	WBTC	8
0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2	WETH	18

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

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

必要なデータが揃ったので集約するためのベーステーブルを作成しましょう。以下のコードでWBTCという名前のテーブルを作成します。WBTCの部分をUSDCに書き換えることでWETH-USDCプールを対象としたベーステーブルが作成できます。

-- スワップイベントのトランザクションデータを取得する
WITH wbtc_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 BETWEEN '2022-04-01 00:00:00' AND '2022-04-15 23:59:59'
     AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')  -- the WBTC-WETH pool on Sushiswap’s contract address
     AND event_name IN ('Swap')
),
-- SushiswapのWBTC-WETHプールのメタデータを取得する
wbtc_pools AS (
  SELECT
  	pool_name, pool_address, token0, token1
  FROM
  	ETHEREUM.CORE.DIM_DEX_LIQUIDITY_POOLS
  WHERE
  	pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
),
-- 各トークンの小数点桁数を取得する
wbtc_dicimals AS(
  SELECT
  	address, symbol, decimals
  FROM
  	ETHEREUM.CORE.DIM_CONTRACTS
  WHERE
  	address = (SELECT  LOWER(token1) FROM wbtc_pools)
    OR address = (SELECT LOWER(token0) FROM wbtc_pools)
),
-- 各トークンの小数点桁数を結合する
wbtc_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
  	wbtc_pools
  LEFT JOIN wbtc_dicimals AS token0
  ON token0.address = token0
  LEFT JOIN wbtc_dicimals AS token1
  ON token1.address = token1
),
-- トランザクションデータにメタデータを結合する
wbtc_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
    wbtc_swaps
    LEFT JOIN wbtc_pool_token_details
    ON contract_address = pool_address
),
-- 各トークンの小数点を指定した桁数に変換する
wbtc_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
  	wbtc_swaps_contract_details
)
-- 集約する
SELECT
  DATE_TRUNC('day', block_timestamp) AS DATE,
  COUNT(tx_hash) AS wbtc_swap_count,
  SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS wbtc_vol
FROM
  wbtc_final_details
GROUP BY DATE
;

4. データを集約する

最後にWBTCとUSDCの二つのベーステーブルを結合します。最終的に完成したクエリが以下です。

-- スワップイベントのトランザクションデータを取得する
WITH wbtc_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 BETWEEN '2022-04-01 00:00:00' AND '2022-04-15 23:59:59'
     AND contract_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')  -- the WBTC-WETH pool on Sushiswap’s contract address
     AND event_name IN ('Swap')
),
-- SushiswapのWBTC-WETHプールのメタデータを取得する
wbtc_pools AS (
  SELECT
  	pool_name, pool_address, token0, token1
  FROM
  	ETHEREUM.CORE.DIM_DEX_LIQUIDITY_POOLS
  WHERE
  	pool_address = LOWER('0xceff51756c56ceffca006cd410b03ffc46dd3a58')
),
-- 各トークンの小数点桁数を取得する
wbtc_dicimals AS(
  SELECT
  	address, symbol, decimals
  FROM
  	ETHEREUM.CORE.DIM_CONTRACTS
  WHERE
  	address = (SELECT  LOWER(token1) FROM wbtc_pools)
    OR address = (SELECT LOWER(token0) FROM wbtc_pools)
),
-- 各トークンの小数点桁数を結合する
wbtc_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
  	wbtc_pools
  LEFT JOIN wbtc_dicimals AS token0
  ON token0.address = token0
  LEFT JOIN wbtc_dicimals AS token1
  ON token1.address = token1
),
-- トランザクションデータにメタデータを結合する
wbtc_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
    wbtc_swaps
    LEFT JOIN wbtc_pool_token_details
    ON contract_address = pool_address
),
-- 各トークンの小数点を指定した桁数に変換する
wbtc_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
  	wbtc_swaps_contract_details
),
-- 集約する
wbtc AS (
  SELECT
   DATE_TRUNC('day', block_timestamp) AS DATE,
   COUNT(tx_hash) AS wbtc_swap_count,
   SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS wbtc_vol
  FROM
   wbtc_final_details
  GROUP BY DATE
  ),


  
-- スワップイベントのトランザクションデータを取得する
usdc_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 BETWEEN '2022-04-01 00:00:00' AND '2022-04-15 23:59:59'
     AND contract_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')  -- the WBTC-WETH pool on Sushiswap’s contract address
     AND event_name IN ('Swap')
),
-- SushiswapのWBTC-WETHプールのメタデータを取得する
usdc_pools AS (
  SELECT
  	pool_name, pool_address, token0, token1
  FROM
  	ETHEREUM.CORE.DIM_DEX_LIQUIDITY_POOLS
  WHERE
  	pool_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0')
),
-- 各トークンの小数点桁数を取得する
usdc_dicimals AS(
  SELECT
  	address, symbol, decimals
  FROM
  	ETHEREUM.CORE.DIM_CONTRACTS
  WHERE
  	address = (SELECT  LOWER(token1) FROM usdc_pools)
    OR address = (SELECT LOWER(token0) FROM usdc_pools)
),
-- 各トークンの小数点桁数を結合する
usdc_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
  	usdc_pools
  LEFT JOIN usdc_dicimals AS token0
  ON token0.address = token0
  LEFT JOIN usdc_dicimals AS token1
  ON token1.address = token1
),
-- トランザクションデータにメタデータを結合する
usdc_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
    usdc_swaps
    LEFT JOIN usdc_pool_token_details
    ON contract_address = pool_address
),
-- 各トークンの小数点を指定した桁数に変換する
usdc_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
  	usdc_swaps_contract_details
),

-- 集約する
usdc AS (
  SELECT
   DATE_TRUNC('day', block_timestamp) AS DATE,
   COUNT(tx_hash) AS usdc_swap_count,
   SUM(amount0In_ADJ) + SUM(amount0Out_ADJ) AS usdc_vol
  FROM
   usdc_final_details
  GROUP BY DATE
  )

-- 2つのテーブルを結合する
SELECT
  wbtc.DATE, 
  wbtc.WBTC_SWAP_COUNT,
  wbtc.WBTC_VOL,
  usdc.USDC_SWAP_COUNT,
  usdc.USDC_VOL
FROM
  wbtc
LEFT JOIN usdc
  ON wbtc.DATE = usdc.DATE
ORDER BY wbtc.DATE ASC
;

クエリについては以下のページで公開しています。
https://app.flipsidecrypto.com/velocity/queries/102d96f4-5dcf-4562-b6b3-653b8fe83fe2