ダッシュボードレポート - 受け取ったいいねの数が多いトップユーザー

データとレポート
, ,
1

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

このダッシュボードレポートは、サイト上で「いいね！」を最も多く受け取ったユーザーを、すべての投稿にわたる「いいね！」の数で示します。このレポートは、管理者が特定の期間内に受け取った「いいね！」をカウントすることで、エンゲージメントの高いユーザーを特定するのに役立ち、アクティブなメンバーの認識、コンテンツの質の評価、ユーザーエンゲージメントのトレンドの監視、モデレーターの選定などの情報に基づいたコミュニティ管理の意思決定を支援します。

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

SELECT
ua.user_id AS user_id,
u.username as username,
COUNT(*) likes_received
FROM user_actions ua
INNER JOIN users u on ua.user_id = u.id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
AND ua.action_type = 2
GROUP BY ua.user_id, u.username, u.uploaded_avatar_id
ORDER BY likes_received DESC

SQLクエリの説明

クエリは次のように機能します。

  • 日付パラメータ: クエリは、レポートの日付範囲を定義する :start_date と :end_date の2つのパラメータを受け入れます。両方の日付パラメータは YYYY-MM-DD の日付形式を受け入れます。
  • データの選択: クエリは、user_actions テーブル (ua) と users テーブル (u) からユーザーIDとユーザー名を選択します。これらは、ua.user_id が u.id と一致するという条件で、SQLの INNER JOIN コマンドを使用して結合されます。
  • データのフィルタリング: WHERE 句は、指定された日付範囲内のアクションをフィルタリングするために使用され、action_type = 2 で表される was_liked タイプのアクションのみを考慮します。
  • 集計: GROUP BY 句と組み合わされた COUNT(*) 関数は、結果を user_id、username、uploaded_avatar_id ごとにグループ化し、各ユーザーが受け取った「いいね！」の数をカウントします。
  • 結果の並べ替え: ORDER BY 句は、集計されたデータを likes_received に基づいて降順で並べ替えるため、最も多くの「いいね！」を受け取ったユーザーがレポートの最上位に表示されます。

クエリは、指定された日付範囲で受け取った「いいね！」の数とともにユーザーのリストを生成し、最も多いものから最も少ないものへとソートされます。

結果例

user username likes_received
user1 user1 748
user2 user2 324
user3 user3 308
… … …
「いいね！」 2