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
andend_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
- 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.
- 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.
- 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. - 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 |
… | … | … |