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

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')

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

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')

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

-- 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)

-- 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;

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

count(tx_hash) as swap_count

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