目的

トランザクションやユーザー行動を分析することで、ブロックチェーン経済の動向を理解する。
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