過去1年間のグループのメンション数とメンバー数を取得する方法

コミュニティ データとレポート
,
1

こんにちは、私はHopscotch フォーラムのモデレーターです。以下の Data Explorer クエリについてお手伝いいただけないでしょうか。

  • 過去 365 日間にグループが受け取った @ メンションの数
    • 少ない順にソート
    • グループの作成日が 365 日以上前である場合のみ（つまり、グループが 365 日以上存在している場合）
  • 過去 365 日間にグループが受け取った @ メンションの数
    • 過去 365 日間のメンション数が 5 件未満のグループに限定
    • 少ない順にソート
    • グループの作成日が 365 日以上前である場合のみ（つまり、グループが 365 日以上存在している場合）
  • グループのメンバー数
    • メンバー数が 10 人未満のグループに限定
    • 少ない順にソート

目的は、非アクティブなグループの削除を進めることです（クエリは管理者に作成を依頼する予定です）。

よろしくお願いいたします。
Tracey

（data-explorer タグを追加したいのですが、現時点では追加できないようです）

2

こんにちは、

確認ですが、以下の3つのクエリが必要ということでしょうか？

  1. 365日以上経過しているすべてのグループについて、言及回数を昇順にリストアップする
  2. 365日以上経過しているすべてのグループについて、言及回数が5回未満の場合のみ、言及回数を昇順にリストアップする
  3. メンバー数が10人未満のすべてのグループについて、メンバー数をリストアップする

もしその通りであれば、お手伝いできます。必要な情報はすべて groups、group_users、group_mentions に保存されています。

3

私の知る限り、Discourse は明示的にメンションの追跡を行っていませんが、トピックに追加されたリンクは追跡しています。投稿内のグループメンションは、topic_links テーブルの url 値として記録され、その形式は /groups/<group_name> のようになります。以下のクエリを使用すると、おおよそご希望のデータが得られるはずです。start_date と end_date パラメータには日付値を指定する必要があります。日付形式は yyyy-mm-dd です。例えば、過去 1 年間のデータを取得する場合、start_date に 2020-01-01、end_date に 2020-12-31 を入力してください。

--[params]
-- date :start_date
-- date :end_date

WITH group_mentions AS (
SELECT
split_part(url, '/', 3) AS group_name
FROM topic_links tl
JOIN topics t ON t.id = tl.topic_id
WHERE internal = true
AND url LIKE '/groups/%'
AND t.deleted_at IS NULL
AND t.archetype = 'regular'
AND tl.created_at::date BETWEEN :start_date AND :end_date
)

SELECT
gm.group_name,
COUNT(gm.group_name) AS mention_count
FROM group_mentions gm
JOIN groups g ON g.name = gm.group_name
WHERE g.created_at::date <= :start_date
GROUP BY gm.group_name
ORDER BY mention_count DESC

このクエリは、通常の投稿に追加されたメンションのみを返します（ただし、ウィスパー投稿に追加されたメンションは含まれます）。個人メッセージに追加されたメンションも結果に含めたい場合は、最初のクエリから AND t.archetype = 'regular' の行を削除してください。

もしこれがご希望のデータ形式と異なる場合はお知らせください。それまでに他の質問への回答がない場合は、明日にでも対応させていただきます。