仪表盘报告 - 信任等级增长

数据与报告
, ,
1

这是“信任级别增长”仪表板报告的 SQL 版本。

此仪表板报告统计指定日期范围内每天达到信任级别 1、2、3 和 4 的用户数量，并帮助管理员跟踪 Discourse 社区中用户的参与度和信任级别进展情况。

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

SELECT
  date(created_at) AS date,
  COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
  COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
  AND created_at <= :end_date
  AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)

SQL 查询说明

该查询通过从 user_histories 表中选择记录来工作，该表记录用户状态的变化，包括他们的信任级别。

以下是查询的细分：

  • 日期参数：查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。
  • date(created_at) AS date：SELECT 语句的这部分从 created_at 时间戳中提取日期，并将其标记为每个记录的“date”。这将用于按天对结果进行分组。
  • COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached：此部分计算从较低信任级别（小于 X）进展到特定信任级别（Y）的用户数量。FILTER 子句用于仅包含满足指定条件的行。::integer 强制转换用于将 previous_value 转换为整数以进行比较。有四个这样的计数列，分别对应于从 1 到 4 的每个信任级别。
  • FROM user_histories：这指定了从中检索数据的表。
  • WHERE created_at >= :start_date AND created_at <= :end_date：这会将记录筛选为仅包含指定日期范围内的记录。
  • AND (action = 2 OR action = 15)：这会将记录筛选为仅包含指示信任级别更改的操作的记录。对应于信任级别更改的特定操作由数字 2 和 15 表示。
  • GROUP BY date(created_at)：这会按日期对结果进行分组，以便您获得指定范围内每天信任级别更改的计数。
  • ORDER BY date(created_at)：这会按日期按时间顺序对结果进行排序。

示例结果

date tl1_reached tl2_reached tl3_reached tl4_reached
2024-01-01 1 3 1 0
2024-01-02 5 1 0 1
2024-01-03 8 2 3 0
… … … … …
4 个赞