仪表板报告 - 收到来自不同人的点赞最多的用户

数据与报告
, ,
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，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
  • 选择：为输出选择的主要列是：
    • p.user_id：发帖人的用户 ID。
    • u.username：相应的用户名。
    • likes：已“点赞”帖子的不同用户 ID (ua.user_id) 的计数，确保计算每个用户的唯一点赞数。
  • 连接：有两个内连接：
    • 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) 排序，最受欢迎的用户排在前面。

示例结果

用户 用户名 点赞数
user1 user1 100
user2 user2 90
user3 user3 80
… … …
3 个赞