目的
トランザクションやユーザー行動を分析することで、ブロックチェーン経済の動向を理解する。
Dune Analyticsを使って、以下の4つの目的でデータ分析を行います。
1、年ごとのトランザクション数の推移
2、年月ごとのトランザクション数
3、利益を最も出したユーザーの抽出
4、最も利益を出したユーザーの年月別トランザクションと利益の推移
1、年ごとのトランザクション数の推移
2022年1月から2025年6月までのトランザクションの量を調べる。
SELECT
DATE_TRUNC('year', block_time) AS transaction_year,
COUNT(*) AS transaction_count
FROM
ethereum.transactions
WHERE
block_time >= TIMESTAMP '2022-01-01' AND block_time < TIMESTAMP '2025-07-01'
GROUP BY 1
ORDER BY 1;
出力結果
| transaction_year | transaction_count |
|---|---|
| 2022-01-01 00:00 | 408543268 |
| 2023-01-01 00:00 | 383100651 |
| 2024-01-01 00:00 | 429745984 |
| 2025-01-01 00:00 | 231446397 |
2022年から2023年は、約4億トランザクションを推移しています。
2025年は1月から6月の6ヶ月までの集計です。
残り6ヶ月も同じトランザクションが増えれば、2年連続で増加していることになります。
2、年月ごとのトランザクション数
今度は、月ごとのトランザクションのデータの集計をしてみます。
SELECT
EXTRACT(YEAR FROM block_time) AS year,
EXTRACT(MONTH FROM block_time) AS month,
COUNT(*) AS transaction_count
FROM ethereum.transactions
WHERE block_time BETWEEN TIMESTAMP'2023-01-01' AND TIMESTAMP'2025-06-30'
GROUP BY 1, 2
ORDER BY 1, 2;
実行結果
| year | month | transaction_count |
|---|---|---|
| 2023 | 1 | 31861364 |
| 2023 | 2 | 29793399 |
| 2023 | 3 | 33108538 |
| 2023 | 4 | 30166979 |
| 2023 | 5 | 33518547 |
| 2023 | 6 | 31628242 |
| 2023 | 7 | 31474016 |
| 2023 | 8 | 31954474 |
| 2023 | 9 | 30490755 |
| 2023 | 10 | 31344189 |
| 2023 | 11 | 32567910 |
| 2023 | 12 | 35192238 |
| 2024 | 1 | 36015517 |
| 2024 | 2 | 32284937 |
| 2024 | 3 | 39151780 |
| 2024 | 4 | 36001242 |
| 2024 | 5 | 35834969 |
| 2024 | 6 | 35046833 |
| 2024 | 7 | 35491830 |
| 2024 | 8 | 33964686 |
| 2024 | 9 | 33858493 |
| 2024 | 10 | 35799578 |
| 2024 | 11 | 36691046 |
| 2024 | 12 | 39605073 |
| 2025 | 1 | 37865791 |
| 2025 | 2 | 35574446 |
| 2025 | 3 | 36924045 |
| 2025 | 4 | 37148623 |
| 2025 | 5 | 41982829 |
| 2025 | 6 | 40523191 |
3、利益を最も出したユーザーの抽出
以下のクエリを実行します。
今回は、単純にどのユーザーが最も利益を上げたのではなく、
イーサリアムに限定して、一番に利益を得たユーザーを調べます。
単純に利益だけを出そうとすると、タイムアウトになり処理ができませんでした。
（もしかしたら、有料ならできるかもしれません。）
WITH trades AS (
SELECT
tx.tx_from AS user_address,
sum(amount_usd) AS profit_usd
FROM dex.trades tx
WHERE tx.block_date BETWEEN TIMESTAMP'2022-01-01' AND TIMESTAMP'2025-06-30'
AND tx.blockchain = 'ethereum'
GROUP BY tx.tx_from
)
SELECT *
FROM trades
ORDER BY profit_usd DESC
LIMIT 1;
実行結果
| user_address | profit_usd |
|---|---|
| 0xae2fc483527b8ef99eb5d9b44875f005ba1fae13 | 112076706788.15433 |
どれくらいの成績なのか比較するために、
上位10位を出してみます。
LIMIT を １ から 10 に変更します。
■実行結果
| user_address | profit_usd |
|---|---|
| 0xae2fc483527b8ef99eb5d9b44875f005ba1fae13 | 112076706788.15422 |
| 0x654fae4aa229d104cabead47e56703f58b174be4 | 49057794008.502464 |
| 0xf480fab87c07ec2ddd68a83ec0319bb466b1afa2 | 39139204573.31087 |
| 0x93793bd1f3e35a0efd098c30e486a860a0ef7551 | 21359982833.445793 |
| 0xf5dea4b05305636dc37442f4bbeea73df2d7788d | 21276009317.41968 |
| 0xfac983fce7ef3cee8ffb2ceb967e1b2362aada00 | 16818618708.972473 |
| 0x24f7ef98522dd61d529464f67bb3ffe96ea8afc2 | 15679605207.898624 |
| 0xc0ffeebabe5d496b2dde509f9fa189c25cf29671 | 14800854501.000374 |
| 0xd1fa51f2db23a9fa9d7bb8437b89fb2e70c60cb7 | 14108071148.554298 |
| 0xb58555fcba6479fced7de1485eb054943a09af7b | 13287548662.731087 |
１位と２位で約２倍の差があります。
また3位と4位の差も約2倍ほど差があります。
4、最も利益を出したユーザーの年月別トランザクションと利益の推移
期間中に最もイーサリアムの利益を出したユーザー名がわかったので、
その月別の推移も調べてみます。
SELECT
EXTRACT(YEAR FROM block_time) AS year,
EXTRACT(MONTH FROM block_time) AS month,
COUNT(*) AS transaction_count,
SUM(amount_usd) AS profit_usd
FROM dex.trades tx
WHERE block_time BETWEEN TIMESTAMP'2022-01-01' AND TIMESTAMP'2025-06-30'
AND tx.blockchain = 'ethereum'
AND tx_from = 0xae2fc483527b8ef99eb5d9b44875f005ba1fae13
GROUP BY 1, 2
ORDER BY 1, 2;
■実行結果
| year | month | transaction_count | profit_usd |
|---|---|---|---|
| 2023 | 3 | 130655 | 373614869.5628542 |
| 2023 | 4 | 316113 | 1047581879.0502157 |
| 2023 | 5 | 564693 | 2939038156.890196 |
| 2023 | 6 | 373105 | 801167950.4174981 |
| 2023 | 7 | 427152 | 1025485831.1196544 |
| 2023 | 8 | 365989 | 1382492345.4070544 |
| 2023 | 9 | 305808 | 874534399.6522863 |
| 2023 | 10 | 321464 | 1168291001.608094 |
| 2023 | 11 | 367624 | 3013360626.3548274 |
| 2023 | 12 | 308377 | 4725895216.399327 |
| 2024 | 1 | 369354 | 4901779583.214554 |
| 2024 | 2 | 301020 | 4027893809.1943026 |
| 2024 | 3 | 330164 | 5953287353.843077 |
| 2024 | 4 | 427035 | 6776009047.943314 |
| 2024 | 5 | 520922 | 5697543092.233551 |
| 2024 | 6 | 497292 | 5319569317.765276 |
| 2024 | 7 | 505557 | 5308787251.644061 |
| 2024 | 8 | 396679 | 6546235011.92172 |
| 2024 | 9 | 405484 | 4165741304.712543 |
| 2024 | 10 | 384746 | 4974159414.169786 |
| 2024 | 11 | 292155 | 5911160169.2157955 |
| 2024 | 12 | 204366 | 6049581139.798097 |
| 2025 | 1 | 235875 | 6589625781.094651 |
| 2025 | 2 | 301264 | 5532836888.813468 |
| 2025 | 3 | 289140 | 3746055211.103636 |
| 2025 | 4 | 292576 | 4674642056.798116 |
| 2025 | 5 | 308986 | 4397461070.098034 |
| 2025 | 6 | 294086 | 4074795205.5453153 |