Average Staff Response Times to Topics

Community Building Data & reporting
3

Yes, you could modify the report as follows to exclude topics created by staff users.

-- [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  -- Exclude topics created by staff
),
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

Modifications Explained:

  1. staff_users CTE: A new CTE is added to identify users who are either admins or moderators. This helps in filtering out topics created by staff in the subsequent steps.
  2. filtered_topics CTE: This CTE now includes a LEFT JOIN with the staff_users CTE to exclude topics where the user_id matches any staff member’s ID. This is done by checking su.id IS NULL, which ensures that the topic was not created by a staff member.