Среднее время ответа сотрудников на темы

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

Этот отчет Data Explorer предназначен для анализа среднего времени ответа сотрудников (администраторов и модераторов) на темы, созданные в указанном диапазоне дат и категории.

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false

WITH filtered_topics AS (
    SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
    FROM topics t
    WHERE
      (
        ':categories' = '0'
        OR t.category_id IN
          (
            SELECT id
            FROM categories
            WHERE id IN(:categories)
              OR (:include_subcategories AND parent_category_id IN(:categories))
          )
      )
      AND t.created_at >= :start_date 
      AND t.created_at < :end_date 
      AND t.archetype = 'regular' 
      AND t.deleted_at IS NULL
),
staff_replies AS (
    SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE (u.admin = true OR u.moderator = true)
      AND p.deleted_at IS NULL
      AND p.post_type = 1
    GROUP BY p.topic_id
),
response_times AS (
    SELECT 
        ft.topic_id,
        ft.created_at AS topic_created_at,
        sr.first_staff_reply,
        ft.category_id as category_id,
        EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
    FROM filtered_topics ft
    LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT 
    topic_id,
    category_id,
    topic_created_at,
    response_time_hours as staff_response_time_hours
FROM response_times
ORDER BY topic_created_at ASC

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

Отчет использует несколько общих табличных выражений (CTE) для разбивки запроса:

  • filtered_topics: Это CTE фильтрует темы на основе входных параметров, таких как диапазон дат, категория и включение подкатегорий. Оно гарантирует, что учитываются только не удаленные темы с архетипом «regular».
  • staff_replies: Это CTE идентифицирует первый ответ, сделанный сотрудником (администратором или модератором), на темы, определенные в CTE filtered_topics. Оно исключает удаленные сообщения и учитывает только основные сообщения (post_type = 1).
  • response_times: Это CTE рассчитывает время ответа, находя разницу между временем создания темы и временем первого ответа сотрудника. Результат преобразуется из секунд в часы.

Финальный оператор SELECT из CTE response_times извлекает ID темы, ID категории, дату создания темы и рассчитанное время ответа сотрудника в часах, сортируя результаты по дате создания темы.

Параметры

  • start_date (date): Начальная дата периода для анализа создания тем.
  • end_date (date): Конечная дата периода для анализа создания тем.
  • categories (int_list): Список ID категорий для фильтрации тем. Если установлено значение 0, включаются все категории.
  • include_subcategories (boolean): Флаг для определения, следует ли включать подкатегории указанных категорий в анализ.

Результаты

  • topic_id: Уникальный идентификатор темы.
  • category_id: Категория, к которой принадлежит тема.
  • topic_created_at: Дата создания темы.
  • staff_response_time_hours: Время, затраченное сотрудниками на ответ на тему в часах. Значение NULL указывает на отсутствие ответа от сотрудников по теме.

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

topic_id category_id topic_created_at staff_response_time_hours
101 5 2024-01-02 1.5
102 5 2024-01-02 3.2
103 12 2024-01-03 NULL
104 12 2024-01-04 0.75
2

Есть ли простой способ исключить темы, созданные сотрудниками?

3

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

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-01-30
-- int_list :categories = 0
-- boolean :include_subcategories = false

WITH staff_users AS (
    SELECT id
    FROM users
    WHERE admin = true OR moderator = true
),
filtered_topics AS (
    SELECT t.id AS topic_id, t.category_id, t.created_at::DATE AS created_at
    FROM topics t
    LEFT JOIN staff_users su ON t.user_id = su.id
    WHERE
      (
        ':categories' = '0'
        OR t.category_id IN
          (
            SELECT id
            FROM categories
            WHERE id IN(:categories)
              OR (:include_subcategories AND parent_category_id IN(:categories))
          )
      )
      AND t.created_at >= :start_date 
      AND t.created_at < :end_date 
      AND t.archetype = 'regular' 
      AND t.deleted_at IS NULL
      AND su.id IS NULL  -- Исключаем темы, созданные сотрудниками
),
staff_replies AS (
    SELECT p.topic_id, MIN(p.created_at) AS first_staff_reply
    FROM posts p
    JOIN staff_users su ON p.user_id = su.id
    WHERE p.deleted_at IS NULL
      AND p.post_type = 1
    GROUP BY p.topic_id
),
response_times AS (
    SELECT 
        ft.topic_id,
        ft.created_at AS topic_created_at,
        sr.first_staff_reply,
        ft.category_id as category_id,
        EXTRACT(EPOCH FROM (sr.first_staff_reply - ft.created_at)) / 3600 AS response_time_hours
    FROM filtered_topics ft
    LEFT JOIN staff_replies sr ON ft.topic_id = sr.topic_id
)
SELECT 
    topic_id,
    category_id,
    topic_created_at,
    response_time_hours as staff_response_time
FROM response_times
ORDER BY topic_created_at ASC

Объяснение изменений:

  1. CTE staff_users: Добавлен новый CTE для идентификации пользователей, которые являются администраторами или модераторами. Это помогает исключить темы, созданные сотрудниками, на последующих этапах.
  2. CTE filtered_topics: Этот CTE теперь включает LEFT JOIN с CTE staff_users, чтобы исключить темы, где user_id совпадает с ID любого сотрудника. Это достигается проверкой su.id IS NULL, что гарантирует, что тема не была создана сотрудником.
4

Спасибо! Результат теперь гораздо спокойнее.

Чёрт, всё просто… когда есть достаточно навыков.