每日读者和活跃用户

数据与报告
1

此 SQL 报告提供了指定日期范围内读者（仅阅读帖子的用户）和活跃用户（当天至少发布过一帖的用户）的每日计数，并可选择按类别 ID 进行筛选。

本报告旨在帮助管理员了解社区的活跃度，不仅包括内容创建方面，还包括内容消费方面。这种双重视角有助于更详细地了解用户参与度，突出显示阅读量或发帖量高的日子。

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- int :reader_post_read_count = 10
-- null int :category_id

WITH posts_by_user AS (
    SELECT
        p.user_id,
        p.created_at::date AS post_date,
        p.topic_id,
        COUNT(*) AS posts
    FROM posts p
    WHERE p.created_at::date BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, post_date, p.topic_id
), 

posts_read_by_user AS (
    SELECT
        uv.user_id,
        uv.visited_at::date AS visit_date,
        SUM(uv.posts_read) AS posts_read
    FROM user_visits uv
    LEFT JOIN group_users gu ON gu.user_id = uv.user_id
    LEFT JOIN groups g ON g.id = gu.group_id
    LEFT JOIN posts p ON p.user_id = uv.user_id AND p.created_at BETWEEN :start_date AND :end_date
    LEFT JOIN topics t ON t.id = p.topic_id AND t.category_id = :category_id
    WHERE uv.visited_at BETWEEN :start_date AND :end_date
    AND (:category_id IS NULL OR t.category_id IS NOT NULL)
    GROUP BY uv.user_id, visit_date
),

active_users_per_day AS (
    SELECT
        pbu.post_date,
        COUNT(DISTINCT pbu.user_id) AS active_user_count
    FROM posts_by_user pbu
    JOIN topics t ON pbu.topic_id = t.id
    WHERE posts > 0
    AND (:category_id IS NULL OR t.category_id = :category_id)
    GROUP BY post_date
),

readers_per_day AS (
    SELECT
        prbu.visit_date,
        COUNT(DISTINCT prbu.user_id) AS reader_count
    FROM posts_read_by_user prbu
    LEFT JOIN posts_by_user pbu ON prbu.user_id = pbu.user_id AND prbu.visit_date = pbu.post_date
    WHERE pbu.posts IS NULL
    AND prbu.posts_read > :reader_post_read_count
    GROUP BY prbu.visit_date
)

SELECT
    rpd.visit_date,
    rpd.reader_count,
    COALESCE(aupd.active_user_count, 0) AS active_user_count
FROM readers_per_day rpd
LEFT JOIN active_users_per_day aupd ON rpd.visit_date = aupd.post_date
ORDER BY rpd.visit_date

SQL 查询说明

参数

  • start_date 和 end_date：定义分析的时间范围。
  • reader_post_read_count：根据用户阅读帖子的数量，设置将某人视为“读者”的阈值。
  • category_id：可选。指定后，报告将专注于单个类别，从而实现更具针对性的分析。

CTE

  1. posts_by_user：此 CTE 确定在指定日期范围内，每个用户每天创建的帖子数量。这对于了解哪些用户在贡献内容以及这些活动如何随时间分布至关重要。
  2. posts_read_by_user：此 CTE 计算每个用户在指定日期范围内每天阅读的帖子数量。它利用用户访问数据来估算内容消费量，这是参与度的关键指标。
  3. active_users_per_day：基于 posts_by_user CTE，此 CTE 计算每天至少发布一次的独立用户数量。它提供了活跃内容创建者的每日快照。
  4. readers_per_day：使用 posts_read_by_user CTE，此 CTE 识别那些阅读帖子数量超过指定数量（reader_post_read_count）但自己不发帖的用户。它突显了通过阅读参与的沉默大多数。

结果

最终输出呈现了用户参与度的每日概览，详细说明了每天的读者数量和活跃用户（发帖者）数量。

示例结果

visit_date reader_count active_user_count
2024-01-02 150 25
2024-01-03 175 30
2024-01-04 160 20
2024-01-05 180 22
… … …
4 个赞