仪表盘报告 - 新贡献者

数据与报告
, ,
1

这是新贡献者仪表板报告的 SQL 版本。

此 SQL 仪表板报告旨在通过确定在指定日期范围内每天有多少用户首次发帖，为管理员提供用户在 Discourse 论坛上参与度的清晰概览。

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  date_trunc('day', p.created_at)::date AS day,
  COUNT(DISTINCT p.user_id) AS new_contributors
FROM
  posts p
INNER JOIN (
  SELECT
    user_id,
    MIN(created_at) as first_post_date
  FROM
    posts
  WHERE deleted_at IS NULL
  GROUP BY
    user_id
) fp ON p.user_id = fp.user_id
WHERE
  p.created_at = fp.first_post_date
  AND p.created_at BETWEEN :start_date AND (:end_date::date + 1)
GROUP BY
  day
ORDER BY
  day

SQL 查询说明

该报告获取在指定日期范围内每天首次发帖的独立用户计数。它通过执行以下步骤来实现：

参数：

  • 查询接受两个参数 :start_date 和 :end_date，它们定义了报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。

内部查询：确定每个用户的首次发帖日期

此报告包含一个子查询，该子查询从 posts 表中选择两列：user_id 和最早的 created_at 时间戳（别名为 first_post_date）。最早的 created_at 时间戳代表用户的首次发帖。此子查询包含一个 WHERE 条件，该条件仅考虑未被删除的帖子（deleted_at IS NULL）。最后，它按 user_id 对结果进行分组，以确保我们只查看每个用户的首次发帖。

主查询：计算首次发帖的用户数

主查询执行以下操作：

  • JOIN：主 posts 表（别名为 p）与子查询结果（别名为 fp）在 user_id 上连接，以将每个帖子与相应用户的首次发帖匹配。
  • 按日期过滤：WHERE 子句包含两个条件 - 它将每个帖子的 created_at 时间戳与子查询中的 first_post_date 进行比较，以确保我们只处理首次发帖，并检查 created_at 时间戳是否在指定的日期范围内，包括 end_date（+1 天以完全包含结束日期）。
  • 聚合：然后按日期对帖子进行分组，截断到没有时间分量的日期（date_trunc('day', p.created_at)::date），这允许计算每天首次发帖的唯一用户数。
  • 计数：使用 COUNT(DISTINCT p.user_id)，我们获得每天首次在论坛发帖的唯一用户数量。
  • 排序：结果按日期升序排序（ORDER BY day），以提供用户参与度的按时间顺序的概览。

最终输出

最终报告包含两列：

  • day：用户参与的日期，不含时间分量。
  • new_contributors：每天在论坛上首次发帖的独立用户数量。

示例结果

day new_contributors
2023-12-15 16
2023-12-16 8
2023-12-17 7
2023-12-18 19
2023-12-19 15
2 个赞