Вопросы, решённые и назначенные пользователю темы в месяц

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

:discourse: Этот отчёт требует включения плагина Discourse Solved .

Этот отчёт Data Explorer предоставляет обзор деятельности участников конкретной группы за определённый период времени. В частности, он фокусируется на двух основных видах активности: решённые вопросы и назначенные темы.

Отчёт предназначен для того, чтобы помочь администраторам понять вклад и нагрузку участников группы, что облегчает лучшее распределение ресурсов и признание активных участников.

--[params]
-- string :group_name_filter = staff
-- string :date_trunc = month
-- null user_list :user_list
-- boolean :userlist_filter = false
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH group_users_filtered AS (
    SELECT 
        gu.user_id
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    WHERE g.name = :group_name_filter
),
user_groups AS (
    SELECT 
        gu.user_id, 
        STRING_AGG(g.name, ', ') AS group_names
    FROM group_users gu
    JOIN groups g ON g.id = gu.group_id
    GROUP BY gu.user_id
),
questions_solved AS (
    SELECT 
        p.user_id, 
        DATE_TRUNC(:date_trunc, dst.created_at) AS month,
        COUNT(*) AS total_solved
    FROM discourse_solved_solved_topics dst
    JOIN posts p ON p.id = dst.answer_post_id
    JOIN group_users_filtered guf ON guf.user_id = p.user_id
    WHERE dst.created_at BETWEEN :start_date AND :end_date
    GROUP BY p.user_id, DATE_TRUNC(:date_trunc, dst.created_at)
),
assigns_per_user AS (
    SELECT 
        a.assigned_to_id AS user_id, 
        DATE_TRUNC(:date_trunc, a.created_at) AS month,
        COUNT(a.topic_id) AS total_assigned
    FROM assignments a
    JOIN topics t ON t.id = a.topic_id
    JOIN group_users_filtered guf ON guf.user_id = a.assigned_to_id
    WHERE a.assigned_to_type = 'User'
      AND t.deleted_at IS NULL
      AND a.created_at BETWEEN :start_date AND :end_date
    GROUP BY a.assigned_to_id, DATE_TRUNC(:date_trunc, a.created_at)
)
SELECT 
    COALESCE(qs.month, apu.month)::DATE AS date,
    :date_trunc as date_range,
    COALESCE(qs.user_id, apu.user_id, ug.user_id) AS user_id, 
    COALESCE(qs.total_solved, 0) AS total_solved_questions,
    COALESCE(apu.total_assigned, 0) AS total_assigned_topics,
    COALESCE(ug.group_names, '') AS group_names
FROM questions_solved qs
FULL OUTER JOIN assigns_per_user apu ON qs.user_id = apu.user_id AND qs.month = apu.month
LEFT JOIN user_groups ug ON ug.user_id = COALESCE(qs.user_id, apu.user_id)
WHERE (qs.user_id IN(:user_list) OR :userlist_filter = FALSE)
ORDER BY date DESC

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

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

Описание CTE

  1. group_users_filtered: Это CTE идентифицирует пользователей, являющихся членами указанной группы (:group_name_filter). Оно фильтрует пользователей на основе их членства в интересующей группе.
  2. user_groups: Это CTE агрегирует все группы, к которым принадлежит пользователь, в одну строку. Это помогает идентифицировать все группы, связанные с пользователем, предоставляя контекст для его деятельности.
  3. questions_solved: Это CTE вычисляет общее количество решённых вопросов каждым пользователем в указанном диапазоне дат (:start_date — :end_date).
  4. assigns_per_user: Аналогично questions_solved, это CTE подсчитывает количество задач, назначенных каждому пользователю в указанном диапазоне дат. Оно гарантирует, что учитываются только назначения пользователям (а не группам или другим сущностям), и исключает удалённые темы.

Финальный запрос объединяет эти CTE для создания отчёта, который включает дату (или диапазон дат), идентификатор пользователя, общее количество решённых вопросов, общее количество назначенных тем и названия групп, к которым принадлежит пользователь. Он позволяет фильтровать результаты по конкретному списку пользователей, если он предоставлен (:user_list), и может изменять детализацию даты в зависимости от параметра :date_trunc (например, месяц, год).

Параметры

  • group_name_filter (строка): Название группы, по которой фильтруются пользователи; по умолчанию установлено значение «staff». Этот параметр используется для выбора пользователей, являющихся членами конкретной группы.
  • date_trunc (строка): Определяет детализацию временной агрегации для отчёта (например, «месяц», «год», «неделя», «день»). Это влияет на то, как данные группируются по времени в выходных данных.
  • user_list (null user_list): Опциональный список идентификаторов пользователей для дополнительной фильтрации результатов. Если он предоставлен, запрос будет включать данные только для этих пользователей.
  • userlist_filter (логическое значение): Флаг, указывающий, следует ли применять фильтр user_list. Если установлено значение false, параметр user_list игнорируется, и включаются данные всех пользователей указанной группы.
  • start_date (дата): Начальная дата периода, за который требуется отчёт о деятельности.
  • end_date (дата): Конечная дата периода, за который требуется отчёт о деятельности.

Результаты

Отчёт предоставляет следующие столбцы:

  • date: Дата или начало диапазона дат, за который агрегируются данные.
  • date_range: Детализация диапазона дат (например, месяц, год).
  • user_id: Идентификатор пользователя.
  • total_solved_questions: Общее количество вопросов, решённых пользователем.
  • total_assigned_topics: Общее количество задач, назначенных пользователю.
  • group_names: Строка, содержащая названия всех групп, к которым принадлежит пользователь.

:person_tipping_hand: Обратите внимание, что из-за особенностей работы таблицы assignments в Discourse этот отчёт сможет отслеживать только текущие назначенные темы и будет отображать их по дате назначения (или повторного назначения).

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

date date_range user total_solved_questions total_assigned_topics group_names
2023-12-01 month user1 5 3 admins, customer-projects-team, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
2023-11-01 month user2 8 1 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, support, trust_level_2
2023-11-01 month user3 3 4 admins, moderators, trust_level_3, trust_level_4, trust_level_0, trust_level_1, staff, trust_level_2
… … … … … …
2

Это не могло быть приурочено лучше! Мне как раз нужно было сделать что-то подобное, и ВОТ ОНО! :tada:

Спасибо!

Жаль, что нельзя получить статистику по заданиям в прошлом (для закрытых тем). Нельзя иметь всё, я полагаю.