Отчет по панели управления — ежедневные активные пользователи

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

Это SQL-версия отчёта Dashboard о ежедневных активных пользователях.

SQL-отчёт подсчитывает количество уникальных пользователей, которые ставили лайки или создавали публикации на сайте каждый день.

--[params]
-- date :start_date
-- date :end_date

SELECT 
  ua.created_at::date AS day,
  COUNT(DISTINCT ua.user_id) AS engaged_users
FROM user_actions ua
INNER JOIN topics t on t.id = ua.target_topic_id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type IN (1, 4, 5, 12)
GROUP BY ua.created_at::date
ORDER BY ua.created_at::date ASC

Разбор запроса:

  • Параметры даты: Запрос принимает два параметра даты — :start_date и :end_date, которые позволяют указать диапазон для отчёта.
  • Выборка: Запрос выбирает дату действия (ua.created_at::date) и подсчитывает количество уникальных идентификаторов пользователей (COUNT(DISTINCT ua.user_id)), выполнивших действия, присваивая этому счётчику псевдоним engaged_users.
  • Источник данных: Запрос получает данные из таблицы user_actions (ua) и соединяет её с таблицей topics (t), чтобы сосредоточиться на действиях, связанных с темами.
  • Типы действий: Запрос фильтрует действия, включая только те, у которых типы 1, 4, 5 и 12. Эти типы действий соответствуют следующим конкретным активностям:
    • 1: Лайк
    • 4: Новая тема
    • 15: Ответ
    • 12: Новое личное сообщение
  • Группировка и сортировка: Результаты группируются по дате действия и сортируются в порядке возрастания, предоставляя хронологический вид активности.

Пример результатов

day engaged_users
2023-01-01 136
2023-01-02 124
2023-01-03 187
2023-01-04 287
2023-01-05 110
2023-01-06 95
2023-01-07 73
2

Это довольно интересно, когда видишь это в развернутом виде. Было бы неплохо, возможно, создать версию, которая включала бы реакции и голосования (опросы, голосование по темам, голосование за посты — возможно, даже голосование за комментарии к постам). :thinking: