Читатели и активные пользователи в день

Создание сообщества Данные и отчетность
1

Этот SQL-отчет предоставляет ежедневный подсчет читателей (пользователей, которые только читают посты) и активных пользователей (пользователей, создавших хотя бы один пост в течение дня) за указанный период с возможностью фильтрации по идентификатору категории.

Этот отчет предназначен для помощи администраторам в понимании активности сообщества не только с точки зрения создания контента, но и его потребления. Такой двойной подход позволяет получить более детальное представление об вовлеченности пользователей, выделяя дни с высокой читаемостью или активностью по созданию постов.

-- [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: на основе CTE posts_by_user это выражение подсчитывает количество уникальных пользователей, сделавших хотя бы один пост в день. Оно предоставляет ежедневную сводку активных создателей контента.
  4. readers_per_day: используя CTE posts_read_by_user, это выражение идентифицирует пользователей, которые прочитали больше указанного количества постов (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 лайка