Time to response

Community Building Data & reporting

AI-generated summary

Time to Response Calculation

Jeanne_Bertrand asked how Discourse calculates the time to first response in its reports and which database collection is used. simon explained that it is calculated by subtracting the topic’s created_at field from the created_at field of the topic’s first reply.

simon further clarified that there isn’t a specific field in the database for this, but rather a method in the Topic class that calculates the time to first response. He provided a link to the code and an approximate SQL query that can be used in the Data Explorer plugin to calculate the time to first response.

The SQL query provided by simon calculates the time to first response by joining topics and posts tables and filtering for regular topics, non-deleted posts, and posts with a higher post number than 1.

1

Hi Discourse !

I was wondering how you calculate the time to first response in your reports.
Based on what collection from your DB ?

Thank you for your time :slight_smile:
Jeanne

2

Time to first response is calculated by subtracting the value of the topic’s created_at field from the value of the created_at field of the topic’s first reply. The first reply is the first post in a topic that has a post_number greater than 1 and has a post_type of 1. (a post_type of 1 is used to indicate that a post is a ‘regular’ post, and not a small action, moderator action, or whisper post.)

3

@simon thanks for your answer.
I am using the data explorer plugin. Do you know in what table I can find this field_first reply ?
I search for it but in vain :frowning:

Many thanks

Jeanne

4

There isn’t a field in the database for this. Discourse uses a method that’s called on the Topic class to calculate the time to first response. You can see the code for it here: discourse/app/models/topic.rb at main · discourse/discourse · GitHub.

This is approximately the SQL query that is generated by the time_to_first_response method. It can be run as a Data Explorer query:

SELECT t.id AS topic_id, AVG(t.hours)::float AS hours, t.created_at
FROM (
  SELECT t.id, t.created_at::date AS created_at, EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS hours
  FROM topics t
  LEFT JOIN posts p ON p.topic_id = t.id
  WHERE t.archetype = 'regular'
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND p.post_number > 1
  AND p.user_id != t.user_id
  AND p.post_type = 1
  GROUP BY t.id
) t
GROUP BY t.created_at, t.id
ORDER BY hours DESC