# Average Staff Response Times to Topics

**URL:** <https://meta.discourse.org/t/average-staff-response-times-to-topics/306528>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [May 2, 2024, 8:03pm UTC](https://meta.discourse.org/t/average-staff-response-times-to-topics/306528 "2024-05-02T20:03:12Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [May 2, 2024, 10:50pm UTC](https://meta.discourse.org/t/average-staff-response-times-to-topics/306528/3 "2024-05-02T22:50:22Z")

</div>

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

```sql
-- [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.

---

_[View the full topic](https://meta.discourse.org/t/average-staff-response-times-to-topics/306528)._