イーサリアム メインネットのクエリの例

このページでは、イーサリアム メインネットの Blockchain Analytics クエリの例を示します。

BigQuery の使用方法については、BigQuery のドキュメントをご覧ください。

インデックス登録された最初と最後のブロックを表示する

このクエリは、データの更新頻度を示します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

SELECT
  MIN(block_number) AS `First block`,
  MAX(block_number) AS `Newest block`,
  COUNT(1) AS `Total number of blocks`
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks;

結果の例を次に示します。

結果の例
最初のブロック 最新のブロック ブロックの合計数
0 17665654 17665655

過去 6 か月間の 1 日あたりの取引件数を可視化する

このクエリは、過去 6 か月間の各日のトランザクションの合計数を一覧表示します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

SELECT
  TIMESTAMP_TRUNC(block_timestamp, DAY) AS timestamp1, COUNT(1) AS txn_count
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions
WHERE
  block_timestamp >= CAST(DATE_SUB(CURRENT_DATE(), INTERVAL 6 MONTH) AS TIMESTAMP)
GROUP BY timestamp1
ORDER BY timestamp1

結果の例を次に示します。

結果の例
timestamp1 txn_count
2023-01-10 00:00:00.000000 UTC 1061055
2023-01-11 00:00:00.000000 UTC 1083178
2023-01-12 00:00:00.000000 UTC 1085563
2023-01-13 00:00:00.000000 UTC 1076328
2023-01-14 00:00:00.000000 UTC 1107804
2023-01-15 00:00:00.000000 UTC 1000777
2023-01-16 00:00:00.000000 UTC 1057284
2023-01-17 00:00:00.000000 UTC 1018353
2023-01-18 00:00:00.000000 UTC 1118225
2023-01-19 00:00:00.000000 UTC 1007125
2023-01-20 00:00:00.000000 UTC 1024504

1 日あたりのスロット使用率

The Merge 以降、各暦日に追加されたブロックの数をカウントします。それ以降、ブロックに使用できるスロットは 7,200 個ありますが、すべてのスロットが使用されているわけではありません。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

SELECT
  DATE(block_timestamp) AS block_date,
  COUNT(block_number) AS daily_blocks,
  7200 - COUNT(block_number) AS skipped_slots
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks
WHERE
  DATE(block_timestamp) BETWEEN DATE("2022-09-16") AND CURRENT_DATE("UTC") - 1 /* Only count complete days after The Merge */
GROUP BY block_date

結果の例を次に示します。

結果の例
block_date daily_blocks skipped_slots
2023-06-26 7105 95
2023-06-25 7109 91
2023-06-24 7110 90
2023-06-23 7111 89
2023-06-22 7114 86
2023-06-21 7135 65
2023-06-20 7120 80
2023-06-19 7121 79
2023-06-18 7126 74
2023-06-17 7142 58

Total Staked ETH withdrawal（ステークされた ETH の引き出し合計）

UINT256 の UDF ワークアラウンドを使用したロスレスの例

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

WITH withdrawals AS (
  SELECT
    w.amount_lossless AS amount,
    DATE(b.block_timestamp) AS block_date
  FROM
    bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks AS b
    CROSS JOIN UNNEST(withdrawals) AS w
)
SELECT
  block_date,
  bqutil.fn.bignumber_div(bqutil.fn.bignumber_sum(ARRAY_AGG(amount)), "1000000000") AS eth_withdrawn
FROM
  withdrawals
GROUP BY 1 ORDER BY 1 DESC

非可逆圧縮の例

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

WITH withdrawals AS (
  SELECT
    u.amount AS amount
  FROM
    bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks
    CROSS JOIN UNNEST(withdrawals) AS u
)
SELECT
  SUM(withdrawals.amount) / POW(10,9) AS total_eth_withdrawn
FROM
  withdrawals

EIP-1559 以降のマイニング トランザクション手数料の収益

EIP-1559 以降、トランザクションの基本料金はバーンされ、マイナーは優先料金のみを獲得します。次のクエリは、ロンドン ハードフォーク以降にマイナーが獲得した手数料の合計額を計算します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

WITH tgas AS (
  SELECT t.block_number, gas_used, effective_gas_price FROM
    bigquery-public-data.goog_blockchain_ethereum_mainnet_us.receipts AS r
    JOIN bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions AS t
    ON t.block_number = r.block_number AND t.transaction_hash = r.transaction_hash
)
SELECT
  /* Cast needed to avoid INT64 overflow when doing multiplication. */
  SUM(CAST(tgas.effective_gas_price - b.base_fee_per_gas AS BIGNUMERIC) * tgas.gas_used)
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks b JOIN tgas
  ON b.block_number = tgas.block_number
WHERE
  b.block_number >= 12965000 /* The London hard fork. */

結果の例を次に示します。

結果の例
f0_
645681358899882340722378

スキップされた Beacon Chain スロット

ビーコン チェーンのアップグレード以降に、欠落、フォーク、スキップされたスロットのエポック番号とスロット番号を特定します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

CREATE TEMP FUNCTION SlotNumber(slot_time TIMESTAMP) AS (
  (SELECT DIV(TIMESTAMP_DIFF(slot_time, "2020-12-01 12:00:23 UTC", SECOND), 12))
);

CREATE TEMP FUNCTION EpochNumber(slot_time TIMESTAMP) AS (
  (SELECT DIV(SlotNumber(slot_time), 32))
);

/* Beacon Chain slot timestamps. */
WITH slots AS (
  /* Directly generate the first day's slots. */
  SELECT * FROM UNNEST(GENERATE_TIMESTAMP_ARRAY("2020-12-01 12:00:23 UTC", "2020-12-01 23:59:59 UTC", INTERVAL 12 SECOND)) AS slot_time
  UNION ALL
  /* Join dates and times to generate up to yesterday's slots. Attempting this directly overflows the generator functions. */
  SELECT TIMESTAMP(DATETIME(date_part, TIME(time_part))) AS slot_time
  FROM UNNEST(GENERATE_DATE_ARRAY("2020-12-02", CURRENT_DATE("UTC") - 1)) AS date_part
  CROSS JOIN
  UNNEST(GENERATE_TIMESTAMP_ARRAY("1970-01-01 00:00:11 UTC", "1970-01-01 23:59:59 UTC", INTERVAL 12 SECOND)) AS time_part
)
SELECT
  EpochNumber(slot_time) AS epoch,
  SlotNumber(slot_time) AS slot,
  slot_time,
  FORMAT("https://beaconcha.in/slot/%d", SlotNumber(slot_time)) AS beaconchain_url,
FROM
  slots LEFT JOIN bigquery-public-data.goog_blockchain_ethereum_mainnet_us.blocks
  ON slot_time = block_timestamp
WHERE
  block_number IS NULL AND slot_time BETWEEN "2022-09-15 06:42:59 UTC" AND CURRENT_TIMESTAMP()
ORDER BY slot_time DESC;

結果の例を次に示します。

結果の例
epoch スロット slot_time beaconchain_url
211159 6757113 2023-06-27 23:42:59 UTC https://beaconcha.in/slot/6757113
211159 6757088 2023-06-27 23:37:59 UTC https://beaconcha.in/slot/6757088
211158 6757061 2023-06-27 23:32:35 UTC https://beaconcha.in/slot/6757061
211145 6756660 2023-06-27 22:12:23 UTC https://beaconcha.in/slot/6756660
211145 6756642 2023-06-27 22:08:47 UTC https://beaconcha.in/slot/6756642
211142 6756564 2023-06-27 21:53:11 UTC https://beaconcha.in/slot/6756564
211136 6756379 2023-06-27 21:16:11 UTC https://beaconcha.in/slot/6756379
211136 6756374 2023-06-27 21:15:11 UTC https://beaconcha.in/slot/6756374
211135 6756320 2023-06-27 21:04:23 UTC https://beaconcha.in/slot/6756320
211132 6756225 2023-06-27 20:45:23 UTC https://beaconcha.in/slot/6756225

USDC トークンの発行

2023 年 3 月第 1 週の USDC の純発行額を分析します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

CREATE TEMP FUNCTION IFMINT(input STRING, ifTrue ANY TYPE, ifFalse ANY TYPE) AS (
  CASE
    WHEN input LIKE "0x40c10f19%" THEN ifTrue
    ELSE ifFalse
  END
);

CREATE TEMP FUNCTION USD(input FLOAT64) AS (
  CAST(input AS STRING FORMAT "$999,999,999,999")
);

SELECT
  DATE(block_timestamp) AS `Date`,
  USD(SUM(IFMINT(input, 1, -1) * CAST(CONCAT("0x", LTRIM(SUBSTRING(input, IFMINT(input, 75, 11), 64), "0")) AS FLOAT64) / 1000000)) AS `Total Supply Change`,
FROM
  bigquery-public-data.goog_blockchain_ethereum_mainnet_us.transactions
WHERE
  DATE(block_timestamp) BETWEEN "2023-03-01" AND "2023-03-07"
  AND to_address = "0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48" -- USDC Coin Token
  AND (
    input LIKE "0x42966c68%" -- Burn
    OR input LIKE "0x40c10f19%" -- Mint
  )
GROUP BY `Date`
ORDER BY `Date` DESC;

結果の例を次に示します。

結果の例
日付 Total Supply Change
2023-03-07 -$257,914,457
2023-03-06 -$223,014,422
2023-03-05 $200,060,388
2023-03-04 $234,929,175
2023-03-03 $463,882,301
2023-03-02 $631,198,459
2023-03-01 $172,338,818

USDC アカウント残高上位 10 件

USDC トークンの現在のトップホルダーを分析します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

WITH Transfers AS (
  SELECT address token, to_address account, 0 _out, CAST(quantity AS BIGNUMERIC) _in
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`

  UNION ALL

  SELECT address token, from_address account, CAST(quantity AS BIGNUMERIC) _out, 0 _in
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`
)

/* Top 10 Holders of USDC */
SELECT account, (SUM(_in) - SUM(_out)) / 1000000 balance
FROM Transfers
WHERE token = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
GROUP BY account
ORDER BY balance DESC
LIMIT 10;

結果の例を次に示します。

結果の例
アカウント 残高
0xcee284f754e854890e311e3280b767f80797180d 934249404.099105
0x40ec5b33f54e0e8a33a975908c5ba1c14e5bbbdf 608860969.753471
0x47ac0fb4f2d84898e4d9e7b4dab3c24507a6d503 422999999.84
0x0a59649758aa4d66e25f08dd01271e891fe52199 382469988.743467
0xd54f502e184b6b739d7d27a6410a67dc462d69c8 335866305.446392
0x99c9fc46f92e8a1c0dec1b1747d010903e884be1 300569267.063296
0xda9ce944a37d218c3302f6b82a094844c6eceb17 231000000
0x51edf02152ebfb338e03e30d65c15fbf06cc9ecc 230000000.000002
0x7713974908be4bed47172370115e8b1219f4a5f0 218307714.860457
0x78605df79524164911c144801f41e9811b7db73d 211737271.4

BAYC トークンのアクティブなトレーダー上位 5 人

最も多くの Bored Ape NFT を転送した EOA を分析します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

WITH Transfers AS (
  SELECT address AS token, to_address AS account, COUNT(*) transfer_count
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`
  GROUP BY token, account

  UNION ALL

  SELECT address AS token, from_address AS account, COUNT(*) transfer_count
  FROM `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.token_transfers`
  WHERE from_address != '0x0000000000000000000000000000000000000000'
  GROUP BY token, account
)

SELECT account, SUM(transfer_count) quantity
FROM Transfers LEFT JOIN `bigquery-public-data.goog_blockchain_ethereum_mainnet_us.accounts` ON account = address
WHERE NOT is_contract AND token = '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d' /* BAYC */
GROUP BY account
ORDER BY quantity DESC
LIMIT 5;

結果の例を次に示します。

結果の例
アカウント quantity
0xed2ab4948ba6a909a7751dec4f34f303eb8c7236 6036
0x020ca66c30bec2c4fe3861a94e4db4a498a35872 2536
0xd387a6e4e84a6c86bd90c158c6028a58cc8ac459 2506
0x8ae57a027c63fca8070d1bf38622321de8004c67 2162
0x721931508df2764fd4f70c53da646cb8aed16ace 968

Uniswap での USDC での WETH の 1 日あたりの平均価格

Uniswap USDC/WETH 0.05% 手数料プールでの 1 日の平均スワップ価格を確認します。

Google Cloud コンソールで、[BigQuery] ページに移動します。

BigQuery に移動

次のクエリが [エディタ] フィールドに読み込まれます。

With Swaps AS (
  SELECT block_timestamp, transaction_hash,
    STRING(args[0]) sender,
    STRING(args[1]) recipient,
    SAFE_CAST(STRING(args[2]) AS BIGNUMERIC) amount0, /* USDC amount */
    SAFE_CAST(STRING(args[3]) AS BIGNUMERIC) amount1, /* WETH amount */
    SAFE_CAST(STRING(args[4]) AS BIGNUMERIC) sqrtPriceX96,
    CAST(STRING(args[5]) AS BIGNUMERIC) liquidity,
    CAST(STRING(args[6]) AS INT64) tick
  FROM `bigquery-public-data.blockchain_analytics_ethereum_mainnet_us.decoded_events`
  WHERE event_signature = 'Swap(address,address,int256,int256,uint160,uint128,int24)' /* Uniswap v3 Swaps */
  AND address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' /* USDC/ETH 0.05% Pool */
),
EtherUSDC AS (
  SELECT block_timestamp,
    ABS(amount0) / 1000000 usdc_amount, /* USDC uses 6 decimals */
    ABS(amount1) / 1000000000000000000 eth_amount, /* WETH uses 18 decimals */
    ABS(SAFE_DIVIDE(amount0, amount1)) * 1000000000000 usd_eth /* USDC/ETH has 12 decimal difference */
  FROM Swaps
)

SELECT EXTRACT(DATE FROM block_timestamp) `date`, CAST(AVG(usd_eth) AS STRING FORMAT '$9,999.00') `avg_price`, COUNT(*) `swap_count`
FROM EtherUSDC
WHERE usdc_amount >= 1.00 /* Ignore miniscule swaps */
GROUP BY `date`
ORDER BY `date` DESC

結果の例を次に示します。

結果の例
日付 avg_price swap_count
2023-10-03 $1,658.24 3819
2023-10-02 $1,704.98 5136
2023-10-01 $1,689.63 3723
2023-09-30 $1,675.90 2988
2023-09-29 $1,665.99 4173