Отчет панели управления - Посещения пользователей

[SaraDev (Sara Devlaeminck)]

Это SQL-версия отчёта панели управления по посещениям пользователей и посещениям пользователей (мобильные устройства).

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

Понимание паттернов посещений пользователей помогает администраторам принимать обоснованные решения для повышения вовлечённости пользователей и здоровья сообщества.

-- [params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- null text :group_name
-- boolean :mobile_views = false

SELECT uv.visited_at AS date,
       COUNT(DISTINCT uv.user_id) AS visits_count
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 AND (:group_name IS NULL OR g.name = :group_name)
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND (:group_name IS NULL OR g.name IS NOT NULL)
AND (:mobile_views = false OR uv.mobile = true)
GROUP BY uv.visited_at
ORDER BY uv.visited_at ASC

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

SQL-запрос работает путём выборки данных из таблицы user_visits, которая регистрирует каждое посещение пользователями платформы. Основные компоненты запроса объяснены ниже:

• Параметры
  • :start_date и :end_date определяют диапазон дат для отчёта. Оба параметра даты принимают формат YYYY-MM-DD.
  • :group_name позволяет фильтровать по имени конкретной группы пользователей. Если не указано, включаются данные по всем группам.
  • :mobile_views определяет, включать ли только посещения с мобильных устройств (true) или все посещения (false).
• Выборка данных и операции соединения:
  • Запрос выбирает дату посещения (visited_at) и подсчитывает уникальные идентификаторы пользователей (user_id) для вычисления количества уникальных посещений в день.
  • Выполняется LEFT JOIN с таблицей group_users для связывания посещений пользователей с их соответствующими группами, за которым следует ещё один LEFT JOIN с таблицей groups для фильтрации по указанному имени группы, если оно задано.
• Фильтрация:
  • Условие WHERE применяет фильтры на основе входных параметров:
    • Гарантируется, что включаются только посещения в указанном диапазоне дат.
    • Выполняется фильтрация по имени группы, если оно указано, и обеспечивается подсчёт посещений для пользователей, входящих в эту группу.
    • Включаются только мобильные посещения, если параметр :mobile_views установлен в true.
• Группировка и сортировка:
  • Запрос группирует результаты по дате посещения (visited_at) для агрегации количества посещений в день.
  • Результаты сортируются в порядке возрастания по дате, обеспечивая хронологический просмотр посещений пользователей.

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

date	visits_count
2024-01-06	67
2024-01-07	71
2024-01-08	88
2024-01-09	79
2024-01-10	78
…	…