Readers and Active Users Per Day

data & reporting
1

This SQL report provides a daily count of readers (users who only read posts) and active users (users who have created at least one post during the day), over a specified date range, with an optional filter for category id.

This report is designed to help administrators understand how active their community is, not just in terms of content creation but also in content consumption. This dual perspective allows for a more detailed understanding of user engagement, highlighting days with high readership or posting activity.

-- [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 Query Explanation

Parameters

  • start_date and end_date: Define the time frame for the analysis.
  • reader_post_read_count: Sets the threshold for considering someone a “reader” based on the number of posts they’ve read.
  • category_id: Optional. When specified, the report focuses on a single category, allowing for more targeted analysis.

CTEs

  1. posts_by_user: This CTE identifies the number of posts created by each user on each day within the specified date range. It’s crucial for understanding which users are contributing content and how this activity is distributed over time.
  2. posts_read_by_user: This CTE calculates the number of posts read by each user on each day, again within the specified date range. It leverages user visit data to estimate content consumption, a key indicator of engagement.
  3. active_users_per_day: Based on the posts_by_user CTE, this CTE counts the number of unique users who posted at least once per day. It provides a daily snapshot of active content creators.
  4. readers_per_day: Using the posts_read_by_user CTE, this CTE identifies users who read more than a specified number of posts (reader_post_read_count) without posting themselves. It highlights the silent majority who engage by reading.

Results

The final output presents a daily overview of user engagement, detailing the number of readers and active users (those who posted) each day.

Example Results

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 Likes