仪表盘报告 - 信任级别较低用户所获点赞数最多的用户

数据与报告
, ,
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 查询说明

  • 日期参数：
    • 查询接受两个参数 :start_date 和 :end_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 中过滤结果：
    • 仅计算在指定时间段内给出的点赞。
  • 最终选择：
    • 主查询从 CTE 中选择所有列，其中 rank 小于或等于 10。
    • 这将结果限制在每个信任级别类别中收到最多来自较低信任级别用户点赞的前 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 个赞