ダッシュボードレポート - より低い信頼レベルのユーザーからの「いいね！」受信数トップユーザー

データとレポート
, ,
1

ユーザーからの「いいね」が多いトップユーザーのダッシュボードレポートのSQLバージョン（信頼レベルの低いユーザーから）。

このダッシュボードレポートは、指定された期間内に、信頼レベルの低いメンバーから最も多くの「いいね」を受け取ったユーザーを特定することを目的としています。レポートは、異なる信頼レベルのユーザー間のやり取りに焦点を当て、フォーラムでの権限や経験が少ないユーザーからの「いいね」によって際立ったアカウントをハイライトします。

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

WITH user_liked_tl_lower AS (
    SELECT
        users.id AS user_id,
        users.username,
        users.trust_level,
        COUNT(*) AS likes,
        rank() OVER (
            PARTITION BY users.trust_level
            ORDER BY COUNT(*) DESC
        ) AS rank
    FROM users
    INNER JOIN posts p ON p.user_id = users.id
    INNER JOIN user_actions ua ON ua.target_post_id = p.id AND ua.action_type = 1
    INNER JOIN users u_liked ON ua.user_id = u_liked.id AND u_liked.trust_level < users.trust_level
    WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY users.id
    ORDER BY trust_level DESC, likes DESC
)

SELECT * FROM user_liked_tl_lower
WHERE rank <= 10

SQLクエリの説明

  • 日付パラメータ:
    • クエリは、レポートの日付範囲を定義する2つのパラメータ、:start_dateと:end_dateを受け入れます。両方のdateパラメータはYYYY-MM-DD形式の日付を受け入れます。
  • 共通テーブル式（CTE）:
    • CTE user_liked_tl_lowerは、メインクエリ内での再利用のために最初に定義されます。
    • 指定された期間（:start_dateと:end_dateで定義）内に、信頼レベルの低いユーザーから各ユーザーが投稿で受け取った「いいね」の総数を計算します。
  • 内部結合:
    • usersテーブルとpostsおよびuser_actionsテーブルの間で結合が行われ、「いいね」（action_type = 1で示される）であるアクションを除外します。
    • いいねをしたユーザーが投稿者の信頼レベルよりも低いことを確認するために、usersテーブル（u_liked）への追加の結合が行われます。
  • ランキング:
    • 結果はユーザーの信頼レベルでパーティション化され、「いいね」の数で降順に並べ替えられます。
    • 「いいね」の数に基づいてランキングが割り当てられ、同点の場合は同じランクが与えられ、次のランクはスキップされます（これは標準的なランキングであり、密なランキングではありません）。
  • CTEでの結果のフィルタリング:
    • 指定された期間内に付与された「いいね」のみがカウントされます。
  • 最終選択:
    • メインクエリは、rankが10以下のCTEからすべての列を選択します。
    • これにより、信頼レベルの低いユーザーから最も多くの「いいね」を受け取った各信頼レベルカテゴリの上位10ユーザーに結果が制限されます。

結果例

user username trust_level likes rank
user1 user1 4 323 1
user2 user2 4 164 2
user3 user3 4 143 3
… … … … …
user11 user11 3 45 1
user12 user12 3 34 2
… … … … …
「いいね！」 3