Отчет по дашборду — пользователь-пользователь

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта «Дашборд» для сообщений «от пользователя к пользователю» (с ответами) и «от пользователя к пользователю» (без учёта ответов).

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

--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false

SELECT 
    p.created_at::date as day,
    COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
    AND p.user_id > 0
    AND t.subtype = 'user_to_user'
    AND p.deleted_at ISNULL
    AND t.deleted_at ISNULL
    AND p.created_at::date BETWEEN :start_date AND :end_date
    AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date

Объяснение SQL-запроса

Этот SQL-запрос выполняет следующие действия:

• Параметры:
  • :start_date и :end_date — используются для фильтрации данных в заданном диапазоне дат. Оба параметра даты принимают формат YYYY-MM-DD.
  • :include_replies — определяет, следует ли включать в подсчёт ответы на исходные сообщения. По умолчанию установлено в false, чтобы воспроизвести поведение отчёта «от пользователя к пользователю» (без учёта ответов). Установка этого параметра в true воспроизведёт поведение отчёта «от пользователя к пользователю» (с ответами).
• Выборка данных:
  • Запрос выбирает дату created_at из таблицы posts, которая представляет дату создания каждого сообщения.
• Операция соединения:
  • Запрос выполняет внутреннее соединение (INNER JOIN) таблиц posts и topics по их полям id. Это соединение критически важно, так как оно связывает каждое сообщение с соответствующей темой, позволяя отфильтровать только те сообщения, которые являются частью диалога личных сообщений.
• Критерии фильтрации: Запрос применяет несколько фильтров:
  • Учитываются только сообщения, входящие в тему с archetype равным private_message, что гарантирует подсчёт только личных сообщений.
  • Исключаются сообщения, не связанные с пользователем, путём проверки, что user_id больше 0.
  • Результаты дополнительно ограничиваются темами с subtype равным user_to_user, что указывает на личное сообщение между отдельными пользователями.
  • Исключаются любые сообщения или темы, которые были удалены, путём проверки, что deleted_at равно NULL для обеих таблиц.
  • Применяется фильтр по диапазону дат, чтобы включить только сообщения, созданные между :start_date и :end_date.
  • Если :include_replies равно false, учитывается только первое сообщение в каждой теме (p.post_number = 1), что исключает ответы.
• Агрегация: Запрос группирует результаты по дате создания сообщений, что позволяет подсчитать количество сообщений, отправленных в каждый день.
• Сортировка: Наконец, запрос сортирует результаты по дате в порядке возрастания, предоставляя хронологическую последовательность активности личных сообщений «от пользователя к пользователю».

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

day	personal_messages
2023-11-11	92
2023-11-12	57
2023-11-13	345
2023-11-14	124
2023-11-15	56