[8.23] Duneでfriend.techを分析しよう<最終回>

さいごまで見ていただきありがとうございます

今日は7日目のDune講座、最後のdashboard作成を行います。初級から始まりここまで読んでくれた方は本当にありがとうございます。

前回までの講座をまだみていない方はこちらから↓

お知らせ: 初級が1,000PVを突破しました！

[8.17] 形で覚えるDune使い方講座<初級>

[8.18] 形で覚えるDune使い方講座<初級+>

[8.19] 形で覚えるDune使い方講座<中級>

[8.20] 形で覚えるDune使い方講座<中級+>

[8.21] 形で覚えるDune使い方講座<中級++>

[8.22] 形で覚えるDune使い方講座<上級>

本日は最近Duneに対応した「BASE」でいま流行りの「friend.tech」のdashboardの作成をいままで習ったことを活かしてやっていきましょう。1万字超えの過去一ボリュームですが、ぜひ最後まで見ていってください！

Special thanks to @Guss3.eth

※コードブロックを使用して説明しているため、なるべくパソコンからのアクセスをお勧めします。またこの講座を受けるためにはパソコンが必要です。

今後一部有料記事を投稿予定なので、サブスクリプションもぜひご検討いただけると嬉しいです。その他サポートもいただけると嬉しいです↓

ダッシュボードについて

Duneのdashboardとは作成した複数のqueryを1つにまとめて表示できる場所のことを指します。実際にオンチェーン分析を行った結果をquery単体で公開するわけではなくて、queryをさらにdashboardでまとめて公開します。

今回のゴールの完成品はこちら↓

リンク: friend.tech and base activity

↑受講した方はぜひスターを付けに行ってもらえると嬉しいです。

dashboardはqueryの新規作成と同じ「+」ボタンから作成できます。今回の講座ではすべてのqueryが作成し終わってからdashboardを作るので、まずは一緒にそれぞれのqueryを作っていきましょう！

※また要所で復習可能なように該当する構文を説明している講座リンクを適宜貼っています。わからないところは都度復習して習得していきましょう。

合計トランザクション数を取得しよう

まずはfriend.techを利用している人たちの合計トランザクション数を計測してみましょう。そのためにdocsかexplorerからfriend.techのcontractを探す必要があります。

• friend.techのcontractを探す

• Baseのtxが取得できるDuneのデータセットを探す

• 最適なvisualizationにして表示する

friend.techのcontractはBaseのexplorer「BaseScan」で検索してみると出てきました

0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4

次にBaseのtxが取得できるDuneのデータセットを探しましょう。データセットの名前から推測ができたとしても一旦「SELECT * FROM <データセット名>」を少なめのLIMITで試しに全カラムを取ってデータの中身を確認してみましょう。

今回は「base.transactions」を使って取得してみましょう。

SELECT 
    COUNT(block_time) AS All_tx_counts
FROM base.transactions
WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)

注意ポイントをいくつかまとめてみました。

• 「to」が予約語なので「”to”」にする必要がある

• 「to」のカラムの型が「VARBINARY」なので比較先も「VARBINARY」へ変換

• どのtxでも値が存在するblock_timeの数をカウント

tx数は集計したタイミングによって異なるので「BaseScan Friend.tech: Shares」を見てみなさんが受講した時の数字とほぼ一致していたら問題ないです。

次にこのqueryのvisualizationを最適なものにして表示してみましょう。COUNTやAVGなどで取ってきた数値は単一なので「Counter」を使いましょう。

合計ユーザー数を取得しよう

次にfriend.techの合計ユーザー数を取得してみましょう。

ユーザー数の判定方法はcontractを見て行います。friend.techの場合はShareを買うときも売るときも同一のcontractを叩くので、contract側がfromに入ることはありません。よってユーザー側が絶対にfromに入ります。

fromでアドレスをグループ化して、ユニークなアドレスの数をCOUNTで求めるることで取得することができます。

SELECT
    COUNT(DISTINCT("from")) AS count_txs
FROM base.transactions
WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)

COUNT内でGROUP BYが使用できないので、「DISTINCT」を使用している点がここの注意ポイントです。こちらもvisualizationはCOUNTにします。

〇Duneの画像〇

今日のアクティブウォレット数を取得しよう

今日のアクティブウォレットを取得するための「今日」のデータはどうやって条件指定するのでしょうか？

まず今の時間をTIMESTAMP型で取得できる「NOW」という関数があります。今回はこれを利用しましょう。

DATE_TRUNCを利用して日時を丸めるので、なのでNOWにもDATE_TRUNCをかけて同じ形にし、WHEREで探せば今日のアクティブウォレットが取得できます。

SELECT
    COUNT("from") AS active_wallet
FROM base.transactions
WHERE DATE_TRUNC('day', block_time) = DATE_TRUNC('day',NOW())
GROUP BY DATE_TRUNC('day', block_time)
ORDER BY DATE_TRUNC('day', block_time) DESC

friend.techの今日のアクティブウォレットも取得してみましょう。

SELECT
    COUNT("from") AS active_wallet
FROM base.transactions
WHERE DATE_TRUNC('day', block_time) = DATE_TRUNC('day',NOW())
AND "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
GROUP BY DATE_TRUNC('day', block_time)
ORDER BY DATE_TRUNC('day', block_time) DESC

このように今日や昨日といった固定の特定日ではなく、毎日変更する値を取るためには「NOW」を活用することを覚えておいてください。後半にも登場します。

Baseのtx数の推移と比較しよう

次にBaseの日間トランザクション数との比較を行いましょう。またfriend.techのトランザクション数がBaseの何%を占めているかも計算しましょう。

• Baseのtx数を日別で取得

• friend.techのtx数を日別で取得

• friend.tech tx / Base tx を求める

• 2つのテーブルを結合させる

• 最適なvisualizationにして表示する

まずBaseのtx数を日別で取得して別々のテーブルにする必要があるので、今回はテーブル作成の部分に「WITH句」を使います。またテーブルの結合には「JOIN」を使います。実際の使い方から2つの構文を覚えていきましょう。

最初に必要な情報のみのテーブルをWITH句で作りましょう。WITH句とJOIN句の構文の復習はこちらから↓

[8.22] 形で覚えるDune使い方講座<上級>

• テーブル: BASEのトランザクション

  • カラム: BASEの日付

  • カラム: BASEのトランザクション

• テーブル: friend.techのトランザクション

  • カラム: friend.techの日付

  • カラム: friend.techのトランザクション

それぞれが完成したコードが下記のようになります。カラム名は自由なものを付けて大丈夫です。

WITH 
    base_daily_tx AS (
        SELECT
            DATE_TRUNC('day', block_time) AS b_date,
            COUNT(block_time) AS b_daily_tx
        FROM base.transactions
        GROUP BY DATE_TRUNC('day', block_time)
    ),
    ft_daily_tx AS (
        SELECT
            DATE_TRUNC('day', block_time) AS f_date,
            COUNT(block_time) AS f_daily_tx
        FROM base.transactions
        WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
        GROUP BY DATE_TRUNC('day', block_time)
    )

次にWITH句で作成したテーブルをJOIN句で結合しましょう。friend.techよりBaseの方が前からあるので、2つのテーブルのblock_timeが一致するところでまとめて下記の3つのカラムになるようにしましょう。

• 日付（block_time）

  • ORDER BY DESC で降順に並べ替え

• Baseの日別tx数

• friend.techの日別tx数

では早速やってみましょう、その時のコードは下記のようになります。

SELECT 
    b_date AS "Date",
    b_daily_tx AS Base_txs,
    f_daily_tx AS "friend.tech_txs",
FROM base_daily_tx
JOIN ft_daily_tx
ON base_daily_tx.b_date = ft_daily_tx.f_date
ORDER BY b_date DESC

最後にfriend.tech / Base tx を求めて、Baseのtx全体を占めるfriend.tech tx数の割合を取得しましょう。該当範囲の復習はこちらから↓

[8.20] 形で覚えるDune使い方講座<中級+>

四則演算はそのまま記述して大丈夫で、他のカラムと同様に日別で表示してあげたいのでSELECT内にまずは計算式をいれましょう。

f_daily_tx / b_daily_tx

しかしこのままだと計算できないのでそれぞれを「DOUBLE」に型変換しましょう。

CAST(f_daily_tx AS DOUBLE) / CAST(b_daily_tx AS DOUBLE)

これで計算はできましたが、100%が1で表示されていることに加え、小数点以下の桁数がめちゃくちゃ多いので小数点第二位で四捨五入をしてみましょう。

ROUND(CAST(f_daily_tx AS DOUBLE) / CAST(b_daily_tx AS DOUBLE) * 100, 2)

最後にFORMATを使って末尾に「%」を付けましょう。

FORMAT('%s%%', ROUND(CAST(f_daily_tx AS DOUBLE) / CAST(b_daily_tx AS DOUBLE) * 100, 2)) AS "Percentage of friend.tech"

これにて取得完了です、最後に全てのコードを貼っておきます。

WITH 
    base_daily_tx AS (
        SELECT
            DATE_TRUNC('day', block_time) AS b_date,
            COUNT(block_time) AS b_daily_tx
        FROM base.transactions
        GROUP BY DATE_TRUNC('day', block_time)
    ),
    ft_daily_tx AS (
        SELECT
            DATE_TRUNC('day', block_time) AS f_date,
            COUNT(block_time) AS f_daily_tx
        FROM base.transactions
        WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
        GROUP BY DATE_TRUNC('day', block_time)
    )
SELECT 
    b_date AS "Date",
    b_daily_tx AS Base_txs,
    f_daily_tx AS "friend.tech_txs",
    FORMAT('%s%%', ROUND(CAST(f_daily_tx AS DOUBLE) / CAST(b_daily_tx AS DOUBLE) * 100, 2)) AS "Percentage of friend.tech"
FROM base_daily_tx
JOIN ft_daily_tx
ON base_daily_tx.b_date = ft_daily_tx.f_date
ORDER BY b_date DESC

次に取得したデータからvisualizationを作成してみましょう。今回のような複数のカラムの推移を比較したい場合は、「Line chart」を使うのがオススメです。

グラフ下部の「Result data」から表示する内容を変更できます。今回はすべてのカラムを日別で表示したいので、X軸に「date」、Y軸に3つのカラムを指定しましょう。

これでBaseのtx数の推移と比較は完了です、次のquery作成に進みましょう。

日別アクティブアドレス数の推移を取得しよう

次に日別のアクティブアドレス数（DAU）をBaseとfriend.techのそれぞれで調べてみましょう。そして推移のグラフを作成します。

• Baseのユニークアドレス数を日別で取得

• friend.techのユニークアドレス数を日別で取得

• friend.tech / Base ユニークアドレスを求める

• 2つのテーブルを結合させる

• 最適なvisualizationにして表示する

先程の応用なので説明は省略します、その時のコードは下記のようになります。

WITH
    base_account_counts AS (
        SELECT
            DATE_TRUNC('day', block_time) AS b_date,
            COUNT(DISTINCT("from")) AS b_count_acs
        FROM base.transactions
        GROUP BY DATE_TRUNC('day', block_time)
    ),
    ft_account_counts AS (
        SELECT
            DATE_TRUNC('day', block_time) AS f_date,
            COUNT(DISTINCT("from")) AS f_count_acs
        FROM base.transactions
        WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
        GROUP BY DATE_TRUNC('day', block_time)
    )
SELECT
    b_date AS "Date",
    b_count_acs AS Base_acs,
    f_count_acs AS "friend.tech_acs",
    FORMAT('%s%%', ROUND(CAST(f_count_acs AS DOUBLE) / CAST(b_count_acs AS DOUBLE) * 100, 2)) AS "Percentage of friend.tech"
FROM base_account_counts
JOIN ft_account_counts
ON base_account_counts.b_date = ft_account_counts.f_date
ORDER BY b_date DESC

アドレス毎のtx数をランキングにしよう

ここではfriend.techを利用しているアドレスの中でトランザクションが多い順に並べてランキングにしてみましょう。新規登録ユーザーのShareをすぐに買うBOTがいるみたいなのでこれで探すとaddressの特定ができるかもしれません。

• fromでグループ化してCOUNTで集計

• 集計した数にRANKで順位を付ける

その時のコードは下記のようになります。

SELECT
    RANK() OVER(ORDER BY COUNT(block_time) DESC) AS Ranking,
    "from" AS address,
    COUNT(block_time) AS trades
FROM base.transactions
WHERE "to" = 0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4
GROUP BY "from"

Shareの最高取引額からランキングにしよう

friend.techのShareは高いものだといくらで取引されているんでしょうか？1txの取引額で高かったものから順にランキングにしてみましょう。

• RANKで順位を付ける

• valueが小数点のない値なのでETHのdecimalを考慮して10の18乗倍する

今回新しく登場する「POWER」について説明します。

POWER(<何の><何乗倍に>)

実際にPOWERを使って作成したqueryはこちら。

SELECT
    RANK() OVER(ORDER BY value DESC) AS Ranking,
    "from" AS address,
    CONCAT(CAST(ROUND(value / POWER(10, 18), 4) AS VARCHAR), 'Ξ') AS volume
FROM base.transactions
WHERE "to" = 0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4

前日と比較した今日のtxの増加数を調べよう

いちばん最難関のquery作成を最後の2つに詰め込みました。前日までのデータと現時点のデータを比較して前日からの増加量を調べます。

• それぞれのテーブルを作成

• テーブルを結合させる

• 「- interval '1' day」で一日遡らせる

まずは今日の現時点までのデータを取得してみましょう。テーブルを新たに作成するのでWITH句を使用します。

WITH ft_count_today AS(
    SELECT
        COUNT("from") AS today_count
    FROM base.transactions
    WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
    AND DATE_TRUNC('day', block_time) <= DATE_TRUNC('day', NOW())
)

次に一日前までのデータを取得しましょう。ここでは日時型に対して「interval」で指定した値を足し引きできる仕組みを使用します。

ft_count_yesterday AS(
    SELECT
        COUNT("from") AS yeseterday_count
    FROM base.transactions
    WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
    AND DATE_TRUNC('day', block_time) <= DATE_TRUNC('day', NOW()) - interval '1' day
)

次にこれらの2つのテーブルを結合させましょう。CROSS JOINで交差結合させ、同一の行に2つのデータが並ぶようにしました。

unit_count AS(
    SELECT 
        today_count,
        ft_count_yesterday.yeseterday_count
    FROM ft_count_today
    CROSS JOIN ft_count_yesterday
)

最後に結合させてできた行をもとに、計算を行いましょう。

SELECT 
    FORMAT('%s%%' ,ROUND((CAST(today_count AS DOUBLE) - CAST(yeseterday_count AS DOUBLE)) / CAST(yeseterday_count AS DOUBLE)*100, 3)) AS ratio
FROM unit_count

すべてのコードもこちらに貼っておきます。

WITH ft_count_today AS(
    SELECT
        COUNT("from") AS today_count
    FROM base.transactions
    WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
    AND DATE_TRUNC('day', block_time) <= DATE_TRUNC('day', NOW())
),
ft_count_yesterday AS(
    SELECT
        COUNT("from") AS yeseterday_count
    FROM base.transactions
    WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
    AND DATE_TRUNC('day', block_time) <= DATE_TRUNC('day', NOW()) - interval '1' day
),
unit_count AS(
    SELECT 
        today_count,
        ft_count_yesterday.yeseterday_count
    FROM ft_count_today
    CROSS JOIN ft_count_yesterday
)
SELECT 
    FORMAT('%s%%' ,ROUND((CAST(today_count AS DOUBLE) - CAST(yeseterday_count AS DOUBLE)) / CAST(yeseterday_count AS DOUBLE)*100, 3)) AS ratio
FROM unit_count

前日と比較した今日のアクティブアドレスの増加数を調べよう

先程は全てのtxを数えていましたが、fromのアドレスをグループ化して集計を行います。なのである2箇所にあの文言を足すだけで完了します。

答えは「DISTINCT」です、場所は下のコードを確認してください。

WITH ft_count_today AS(
    SELECT
        COUNT(DISTINCT "from") AS today_count
    FROM
        base.transactions
    WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
    AND DATE_TRUNC('day', block_time) <= DATE_TRUNC('day', NOW())
),
ft_count_yesterday AS(
    SELECT
        COUNT(DISTINCT "from") AS yeseterday_count
    FROM base.transactions
    WHERE "to" = CAST(0xCF205808Ed36593aa40a44F10c7f7C2F67d4A4d4 AS VARBINARY)
    AND DATE_TRUNC('day', block_time) <= DATE_TRUNC('day', NOW()) - interval '1' day
),
unit_count AS(
    SELECT 
        today_count,
        ft_count_yesterday.yeseterday_count
    FROM ft_count_today
    CROSS JOIN ft_count_yesterday
)
SELECT 
    FORMAT('%s%%' ,ROUND((CAST(today_count AS DOUBLE) - CAST(yeseterday_count AS DOUBLE)) / CAST(yeseterday_count AS DOUBLE)*100, 3)) AS ratio
FROM unit_count

dashboardを作成しよう

すべてのqueryが作り終えたので自分だけのdashboardを作成しましょう。最初に紹介したところから新規dashboardを作成してください。

そして右上の「Edit」から「Add visualization」を押すと、作成したqueryがタイルで配置できます。配置は皆さんのセンスにおまかせします！ちなみに自分は最初に貼りましたがこうなりました↓

リンク: friend.tech and base activity

↑受講した方はぜひスターを付けに行ってもらえると嬉しいです。

ぜひここまで完了した方はdashboardのリンクを私のX！に共有いただけますと非常に嬉しいです！すぐスター押しに行きます！！！

まとめ

みなさん本当にお疲れ様でした、Dune講座も今日でお別れです。完結までにもたくさんの方からの引用RTやリプがあったのでかなりやる気出ました。みなさんにとって少しでもオンチェーン分析の興味を持つきっかけになれると嬉しいです。

重ねてになりますが、一旦連載は今日の最終回の講座を持って終了するものの、また総集編を別でまとめて投稿します。おそらくこの土日ぐらいかな？

それでは改めまして1週間に渡り最後までDune講座を読んでいただき本当にありがとうござました。現時点でも1,000人以上の方々に読んでいただけてるので、Duneの布教にも一役買ったのではと思っています。ではまた明日からは私の独り言ニュースレターでお会いしましょう、それでは。