Отчет дашборда — новые участники

Создание сообщества Данные и отчетность
, ,
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 лайка
Admin dashboard report reference guide