ダッシュボードレポート - 様々な人々から受け取った「いいね！」によるトップユーザー

データとレポート
, ,
1

これは、さまざまな人々から受け取った「いいね！」によるトップユーザーのダッシュボードレポートのSQLバージョンです。

このダッシュボードレポートは、指定された期間内に、サイトのさまざまなユーザーから「いいね！」を受け取ったトップユーザーを示しています。このクエリは、どのユーザーが投稿で最も多くの「いいね！」を受け取ったかを調べるように設計されており、各「いいね！」カウントは、投稿を「いいね！」したユニークユーザーの数を表します。

--[params]
--date :start_date = 2024-01-01
--date :end_date = 2025-01-01

SELECT
    p.user_id,
    u.username,
    COUNT(DISTINCT ua.user_id) AS likes
FROM
    user_actions ua
    INNER JOIN posts p ON p.id = ua.target_post_id
    INNER JOIN users u ON p.user_id = u.id
WHERE
    ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type = 1
    AND p.user_id > 0
GROUP BY
    p.user_id,
    u.username
ORDER BY
    likes DESC

SQLクエリの説明

  • 日付パラメータ:
    • このクエリは、レポートの日付範囲を定義する :start_date と :end_date の2つのパラメータを受け入れます。両方の日付パラメータは YYYY-MM-DD の日付形式を受け入れます。
  • 選択: 出力用に選択された主な列は次のとおりです。
    • p.user_id: 投稿者のユーザーID。
    • u.username: 対応するユーザー名。
    • likes: 投稿を「いいね！」したユニークユーザーID (ua.user_id) のカウント。これにより、ユーザーごとのユニークな「いいね！」が計算されます。
  • 結合: 2つの内部結合があります。
    • user_actions ua と posts p: アクションを対応する投稿に一致させます。
    • posts p と users u: 投稿をその作成者に接続し、ユーザー情報の取得を容易にします。
  • フィルタリング:
    • WHERE 句はいくつかのフィルタを適用します。
      • ua.created_at::date BETWEEN :start_date AND :end_date: パラメータ内のアクションにアクションを制限します。
      • ua.action_type = 1: 他のタイプのアクションから「いいね！」アクションを分離します。
      • p.user_id > 0: 未登録ユーザー（システムまたは匿名ユーザーなど）に関連付けられた「いいね！」を除外します。
  • グループ化:
    • 結果セットは p.user_id と u.username でグループ化されます。これにより、いいね！がユーザーと対応するユーザー名ごとに集計されます。
  • 並べ替え:
    • 結果は「いいね！」の数 (likes DESC) で並べ替えられ、最も評価の高いユーザーが最初に表示されます。

結果例

user username likes
user1 user1 100
user2 user2 90
user3 user3 80
… … …
「いいね！」 3