Отчет панели управления - Уведомить пользователя

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта панели управления «Уведомить пользователя».

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

-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16

SELECT
  DATE(created_at) AS day,
  COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'notify_user'
  AND created_at BETWEEN :start_date AND :end_date
  AND deleted_at IS NULL
  AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day 

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

Запрос извлекает данные из таблицы topics — конкретно, тех записей, которые являются личными сообщениями для пользователей с подтипом notify_user в заданном временном диапазоне. Давайте разберём его по частям:

• Параметры даты:
  • Запрос принимает два параметра: :start_date и :end_date, которые определяют диапазон дат для отчёта. Оба параметра даты принимают формат YYYY-MM-DD.
• Выборка и подсчёт:
  • DATE(created_at) преобразует временную метку created_at в формат даты и присваивает ей псевдоним day. Это позволяет группировать и подсчитывать сообщения по дате их создания.
  • COUNT(1) используется для подсчёта количества записей для каждого дня, присваивая этому подсчёту псевдоним notifications_count.
• Критерии фильтрации:
  • Запрос фильтрует только те записи, где archetype равен 'private_message', что гарантирует, что рассматриваются только сообщения, а не другие типы тем.
  • subtype ограничен значением 'notify_user', что выделяет случаи, когда пользователи были уведомлены о помеченных сообщениях.
  • Проверяется, что временная метка created_at сообщения находится в диапазоне между указанными :start_date и :end_date.
  • Сообщения, которые были удалены (deleted_at IS NULL), исключаются из подсчёта, чтобы отчёт отражал только активные коммуникации.
  • Наконец, проверяется, что сообщения связаны с действительным пользователем, путём подтверждения условия user_id > 0.
• Группировка и сортировка:
  • Запрос использует GROUP BY DATE(created_at) для агрегации результатов по каждому дню в заданном диапазоне дат.
  • Затем результаты сортируются в хронологическом порядке с помощью ORDER BY day, что делает отчёт удобным для чтения и интерпретации.

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

day	notifications_count
2023-12-17	1
2023-12-18	1
2023-12-21	1
2023-12-23	1
2023-12-26	1