ダッシュボードレポート - いいね数

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

私のフォーラムはそれほど大きくなく、リアクションのほとんどは「スタッフ」（管理者、モデレーター、TL=4）から来ています。一般ユーザーと「スタッフ」からのいいねの数を比較し、1日あたりの投稿数をリストアップして、何が起こっているのか、そしてリアクションの使用率を向上させるためにどこに力を集中する必要があるのかをよりよく把握したいと思いました。

私と私の仲間であるChatGPTがこれを思いつきました:

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-12-31

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.date::date,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_day, 0) AS posts_per_day
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_non_staff ON dr.date = pa_non_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa_staff ON dr.date = pa_staff.action_date
LEFT JOIN (
  SELECT
    date_trunc('day', p.created_at) AS post_date,
    COUNT(*) AS posts_per_day
  FROM posts p
  WHERE p.created_at >= :start_date
    AND p.created_at <= (:end_date::date + 1)
  GROUP BY post_date
) posts_count ON dr.date = posts_count.post_date
ORDER BY dr.date

@SaraDevの元のクエリからの変更点（ありがとう、Sara！）：
SQL変更点の概要

  1. スタッフグループの生成:
    usersテーブルからスタッフユーザーを特定するためのstaff_users CTEを追加しました。スタッフユーザーは、次のいずれかの条件を満たすユーザーとして定義されます。
    • admin = true
    • moderator = true
    • trust_level = 4
  2. スタッフからのいいねの分離:
    staff_usersグループ内のuser_idでpost_actionsをフィルタリングすることにより、スタッフユーザーからのいいね数を計算するサブクエリを追加しました（staff_likes_count）。
  3. 非スタッフいいね列名の変更:
    非スタッフからのいいねの出力ラベルをlikes_countからregular_likes_countに変更しました。
  4. 合計いいね数の追加:
    regular_likes_countとstaff_likes_countを合計するtotal_likes列を導入しました。
  5. 1日あたりの投稿数の追加:
    1日あたりの投稿数を計算するサブクエリを追加し（posts_per_day）、日付範囲に結合しました。
    （はい、ChatGPTもこの変更点のリストを作成してくれました。）

結果例:

日付 一般ユーザーからのいいね数 スタッフからのいいね数 1日あたりの投稿数
24/1/1 0 6 7
24/1/2 0 5 3
24/1/3 1 0 4
24/1/4 1 2 5
24/1/5 9 9 30
24/1/6 0 1 11
24/1/7 2 4 11
24/1/8 0 5 18
24/1/9 0 0 2
24/1/10 0 0 7
24/1/11 0 4 5
24/1/12 4 0 4
24/1/13 6 0 10
24/1/14 1 7 18
24/1/15 2 4 7
週単位で集計した同じクエリの結果（平滑化のため） 
-- [params]
-- integer :weeks_ago = 52

WITH date_range AS (
  SELECT date_trunc('week', series) AS week_start
  FROM generate_series(
    date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval,
    date_trunc('week', now()),
    '1 week'::interval
  ) series
),
staff_users AS (
  SELECT id
  FROM users
  WHERE admin = true OR moderator = true OR trust_level = 4
)

SELECT
  dr.week_start::date AS week_start,
  COALESCE(pa_non_staff.regular_likes_count, 0) AS regular_likes_count,
  COALESCE(pa_staff.staff_likes_count, 0) AS staff_likes_count,
  COALESCE(pa_non_staff.regular_likes_count, 0) + COALESCE(pa_staff.staff_likes_count, 0) AS total_likes,
  COALESCE(posts_count.posts_per_week, 0) AS posts_per_week
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS regular_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id NOT IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_non_staff ON dr.week_start = pa_non_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', pa.created_at) AS action_week,
    COUNT(*) AS staff_likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.user_id IN (SELECT id FROM staff_users)
    AND pa.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND pa.created_at <= date_trunc('week', now())
  GROUP BY action_week
) pa_staff ON dr.week_start = pa_staff.action_week
LEFT JOIN (
  SELECT
    date_trunc('week', p.created_at) AS post_week,
    COUNT(*) AS posts_per_week
  FROM posts p
  WHERE p.created_at >= date_trunc('week', now()) - (:weeks_ago || ' weeks')::interval
    AND p.created_at <= date_trunc('week', now())
  GROUP BY post_week
) posts_count ON dr.week_start = posts_count.post_week
ORDER BY dr.week_start
興味があれば、Saraのクエリを変更した最終的なプロンプトはこちらです：

2つの日付間の日次いいね数（likes_count）を報告するSQLクエリがありますが、最終的な出力を週単位で集計し、追加の詳細を含めるために、次の拡張を行う必要があります。

  1. スタッフグループの定義:
    • usersテーブルからstaff_usersグループを作成します。ユーザーは、次のいずれかの基準を満たす場合にスタッフと見なされます。
      • admin = true
      • moderator = true
      • trust_level = 4
  2. スタッフと非スタッフによるいいねの分離:
    • 2つの別々の列を追加します。
      • regular_likes_count: 非スタッフユーザーからのいいね数をカウントします。
      • staff_likes_count: スタッフユーザーからのいいね数をカウントします。
    • regular_likes_count列にスタッフユーザーによって生成されたいいね数が含まれないようにします。
  3. 合計いいね数の追加:
    • regular_likes_countとstaff_likes_countを合計するtotal_likes列を含めます。
  4. 期間あたりの投稿数の追加:
    • 各週に作成された投稿数をカウントするposts_per_week列を追加します。
  5. 週単位での集計:
    • クエリを変更して、すべてのデータを日次ではなく週次間隔でグループ化します。
    • 各週の開始日を表すweek_start列を含めます。
  6. 過去N週間の制限:
    • :weeks_agoパラメータを導入して、結果を過去N週間に制限します。デフォルト値は52週間（1年）です。
  7. 順序と最終列:
    • 出力がweek_startで並べ替えられ、次の列がこの順序で含まれていることを確認します。
      1. week_start: 週の開始日。
      2. regular_likes_count: 非スタッフユーザーからのいいね数。
      3. staff_likes_count: スタッフユーザーからのいいね数。
      4. total_likes: regular_likes_countとstaff_likes_countの合計。
      5. posts_per_week: 週に作成された投稿数。