これはモデレーターアクティビティのダッシュボードレポートのSQLバージョンです。
このレポートは、指定された期間内にDiscourseサイトでモデレーターによって実行されたアクティビティの包括的な概要を提供します。レポートは、モデレーターアクティビティの複数の側面を組み合わせています：プラットフォームでの滞在時間、レビューされたフラグの数、作成された投稿、作成された個人メッセージ（PM）、作成されたトピック、および作成された投稿の改訂。
このダッシュボードレポートは、モデレーションチームの効果とエンゲージメントを測定したい管理者にとって貴重なツールであり、彼らのアクティビティと貢献の詳細な分析を提供します。レポートによって提供される洞察は、モデレーターのトレーニング、表彰、および採用に関する意思決定に情報を提供し、コミュニティ基準を維持するためにモデレーションチームが適切にバランスが取れて効果的であることを保証するのに役立ちます。
--[params]
-- date :start_date = 2023-12-01
-- date :end_date = 2024-12-01
-- boolean :include_admins = false
WITH mods AS (
SELECT
id AS user_id,
username_lower AS username,
uploaded_avatar_id
FROM users u
WHERE ((u.moderator = true) OR (:include_admins AND u.admin = true))
AND u.id > 0
),
time_read AS (
SELECT SUM(uv.time_read) AS time_read,
uv.user_id
FROM mods m
JOIN user_visits uv ON m.user_id = uv.user_id
WHERE uv.visited_at >= :start_date
AND uv.visited_at <= :end_date
GROUP BY uv.user_id
),
flag_count AS (
WITH period_actions AS (
SELECT agreed_by_id,
disagreed_by_id
FROM post_actions
WHERE post_action_type_id IN (3,4,8,6,7)
AND created_at >= :start_date
AND created_at <= :end_date
),
agreed_flags AS (
SELECT pa.agreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.agreed_by_id = m.user_id
GROUP BY agreed_by_id
),
disagreed_flags AS (
SELECT pa.disagreed_by_id AS user_id,
COUNT(*) AS flag_count
FROM mods m
JOIN period_actions pa
ON pa.disagreed_by_id = m.user_id
GROUP BY disagreed_by_id
)
SELECT
COALESCE(af.user_id, df.user_id) AS user_id,
COALESCE(af.flag_count, 0) + COALESCE(df.flag_count, 0) AS flag_count
FROM agreed_flags af
FULL OUTER JOIN disagreed_flags df
ON df.user_id = af.user_id
),
revision_count AS (
SELECT pr.user_id,
COUNT(*) AS revision_count
FROM mods m
JOIN post_revisions pr
ON pr.user_id = m.user_id
JOIN posts p
ON p.id = pr.post_id
WHERE pr.created_at >= :start_date
AND pr.created_at <= :end_date
AND p.user_id <> pr.user_id
GROUP BY pr.user_id
),
topic_count AS (
SELECT t.user_id,
COUNT(*) AS topic_count
FROM mods m
JOIN topics t ON t.user_id = m.user_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.user_id
),
post_count AS (
SELECT p.user_id,
COUNT(*) AS post_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'regular'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
),
pm_count AS (
SELECT p.user_id,
COUNT(*) AS pm_count
FROM mods m
JOIN posts p ON p.user_id = m.user_id
JOIN topics t ON t.id = p.topic_id
WHERE t.archetype = 'private_message'
AND p.created_at >= :start_date
AND p.created_at <= :end_date
GROUP BY p.user_id
)
SELECT
m.user_id,
m.username,
fc.flag_count as flags_reviewed,
ROUND(((tr.time_read) / 3600.00),2) as time_reading_hours,
tc.topic_count as topics_created,
pmc.pm_count as PMs_created,
pc.post_count as posts_created,
rc.revision_count as revisions
FROM mods m
LEFT JOIN time_read tr ON tr.user_id = m.user_id
LEFT JOIN flag_count fc ON fc.user_id = m.user_id
LEFT JOIN revision_count rc ON rc.user_id = m.user_id
LEFT JOIN topic_count tc ON tc.user_id = m.user_id
LEFT JOIN post_count pc ON pc.user_id = m.user_id
LEFT JOIN pm_count pmc ON pmc.user_id = m.user_id
ORDER BY m.username ASC
パラメータ
:start_dateおよび:end_date- レポートの期間を定義するパラメータです。両方の日付パラメータはYYYY-MM-DDの日付形式を受け入れます。:include_admins- モデレーターと一緒に管理者をレポートに含めるかどうかを決定するパラメータです。
SQLクエリの説明
レポートは、共通テーブル式（CTE）を使用して、データ処理を管理可能で論理的なセクションに分割するように構造化されています。各CTEで実行されることは次のとおりです。
- mods: モデレーター権限を持つすべてのユーザー、または（
:include_adminsパラメータによって含まれる場合）管理者権限を持つユーザーを特定します。後続のクエリに必要なユーザー列のみを選択します。 - time_read: 提供された開始日と終了日の間に、モデレーターがプラットフォームでコンテンツを読んだ合計時間（秒単位）を計算します。
- flag_count: 指定された期間中にモデレーターが同意または不同意したフラグの数をカウントします。それぞれの投稿アクションタイプIDで表される複数のフラグタイプを考慮に入れます。
- revision_count: 指定された期間内にモデレーターが他のユーザーの投稿に対して行った投稿の改訂回数をカウントします。
- topic_count: モデレーターによって作成された通常のトピックの数をカウントします。
- post_count: 通常のトピック内でモデレーターによって作成された投稿の数をカウントします。
- pm_count: モデレーターによって開始された個人メッセージの数をカウントします。
CTEでデータを収集した後、メインクエリはユーザーIDに基づいてそれらを結合し、各モデレーターのユーザー名、読書に費やした合計時間（時間に変換）、レビューされたフラグの数、作成されたトピック、作成された個人メッセージ、作成された投稿、および作成された改訂を表示する最終レポートをコンパイルします。結果は、モデレーターのユーザー名のアルファベット順に並べ替えられます。
結果例
| user | username | flags_reviewed | time_reading_hours | topics_created | pms_created | posts_created | revisions |
|---|---|---|---|---|---|---|---|
| 1 | moderator1 | NULL | 36.11 | NULL | 344 | 8 | 15 |
| 2 | moderator2 | 46 | 104.52 | 2 | 271 | 466 | 363 |
| 3 | moderator3 | NULL | 72.15 | NULL | 418 | 64 | 16 |
