ダッシュボードレポート - ユーザー間

データとレポート
, ,
1

ユーザー間（返信あり）およびユーザー間（返信なし）のダッシュボードレポートのSQLバージョンです。

このレポートは、指定された期間内にユーザーが毎日送信した個人メッセージの数をカウントし、プライベートコミュニケーションにおけるユーザーエンゲージメントの活動を把握できるようにします。

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

SQLクエリの説明

このSQLクエリは、以下の操作を実行します。

  • パラメータ:
    • :start_date および :end_date: 指定された日付範囲内のデータをフィルタリングするために使用されます。両方のdateパラメータは YYYY-MM-DD の形式を受け入れます。
    • :include_replies: 初期メッセージへの返信をカウントに含めるかどうかを決定するために使用されます。デフォルトでは false に設定されており、ユーザー間（返信なし）レポートの動作を反映します。このパラメータを true に設定すると、ユーザー間（返信あり）レポートの動作を反映します。
  • データ選択:
    • posts テーブルから created_at 日付を選択します。これは、各メッセージが作成された日付を表します。
  • 結合操作:
    • posts テーブルと topics テーブルを id フィールドでINNER JOINします。この結合は、各投稿を対応するトピックにリンクするために不可欠であり、個人メッセージの会話の一部である投稿のみをフィルタリングできるようにします。
  • フィルタリング条件: クエリはいくつかのフィルタを適用します。
    • archetype が「private_message」であるトピックの一部である投稿のみを考慮し、個人メッセージのみがカウントされるようにします。
    • user_id が0より大きいことを確認することで、ユーザーに関連付けられていない投稿を除外します。
    • subtype が「user_to_user」であるトピックの結果をさらに絞り込みます。これは、個々のユーザー間の個人メッセージを示します。
    • 両方のテーブルで deleted_at がNULLであることを確認することで、削除された投稿またはトピックを除外します。
    • 日付範囲フィルタを適用して、:start_date と :end_date の間に作成された投稿のみを含めます。
    • :include_replies が false の場合、各トピックの最初の投稿のみがカウントされ（p.post_number = 1）、返信は除外されます。
  • 集計: クエリは、投稿が作成された日付で結果をグループ化し、各日付に送信されたメッセージ数をカウントできるようにします。
  • ソート: 最後に、クエリは結果を日付の昇順で並べ替え、ユーザー間個人メッセージアクティビティの時系列順を提供します。

結果例

day personal_messages
2023-11-11 92
2023-11-12 57
2023-11-13 345
2023-11-14 124
2023-11-15 56
「いいね！」 4