Dashboard Report - Time to First Response

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for Time to First Response.

This dashboard report provides insights into the average response time to topics within a specified date range. The report calculates the time taken for the first reply to be posted by someone other than the topic creator.

--[params]
-- date :date_start
-- date :date_end

WITH first_reply AS (
    SELECT 
        p.topic_id, 
        MIN(post_number) post_number, 
        t.created_at
    FROM posts p
    INNER JOIN topics t ON p.topic_id = t.id
    WHERE p.deleted_at IS NULL
        AND p.user_id <> t.user_id
        AND p.post_type = 1
        AND p.user_id > 0
        AND t.user_id > 0
        AND t.deleted_at IS NULL
        AND t.archetype = 'regular'
        AND t.created_at::date BETWEEN :date_start AND :date_end
    GROUP BY p.topic_id, t.created_at
)

SELECT 
    fr.created_at::date as day,
    ROUND((EXTRACT(epoch FROM AVG(p.created_at - fr.created_at)) / 3600.00),2) response_time_hours
FROM posts p
INNER JOIN first_reply fr 
    ON fr.topic_id = p.topic_id 
    AND fr.post_number = p.post_number
    AND p.created_at > fr.created_at
GROUP BY fr.created_at::date
ORDER BY day

SQL Query Explanation

The query operates in two stages:

Stage 1: Identifying the First Reply

A Common Table Expression (CTE) named first_reply is used to identify the first reply to each topic. It filters posts to include only those that are not deleted, are of post_type 1 (indicating regular posts), and are created by users other than the topic creator. It also ensures that both the user who posted and the topic creator are not system users (indicated by user_id > 0). The topics considered are non-deleted, of archetype ‘regular’, and created within the specified date range.

The CTE groups the results by topic_id and the topic’s created_at timestamp, selecting the minimum post_number for each topic, which represents the first reply.

Stage 2: Calculating the Average Response Time

The main query calculates the average response time to topics. It joins the posts table with the first_reply CTE on topic_id and post_number to match each topic with its first reply. It also ensures that the reply’s created_at timestamp is after the topic’s created_at timestamp.

The query then groups the results by the day the topic was created and calculates the average response time in hours. This is achieved by subtracting the topic’s created_at timestamp from the reply’s created_at timestamp, converting the result from seconds to hours, and rounding it to two decimal places.

Finally, the results are ordered by the day, providing a daily breakdown of the average response time for the first reply to topics.

Parameters:

  • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.

Example Results

day response_time_hours
2023-11-12 29.87
2023-11-13 81.52
2023-11-14 5.17
2023-11-15 6.51
2023-11-16 7.75
… …
3 Likes