Report Dashboard - Tempo alla prima risposta

Dati e reportistica
, ,
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 :start_date = 2024-01-01
-- date :end_date = 2025-12-31
-- null category_id :category_id
-- boolean :include_subcategories = false
-- null user_id :user_ids 

-- Time to first response by date
SELECT 
  t.created_at AS "date",
  CAST(AVG(t.hours)::numeric(10,2) AS float) AS "response_time_hours"
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
  JOIN posts p ON p.topic_id = t.id
  WHERE 
    t.created_at >= :start_date
    AND t.created_at < :end_date
    AND t.archetype <> 'private_message'
    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  -- Regular post type
    AND EXTRACT(EPOCH FROM p.created_at - t.created_at) > 0
    AND (:category_id IS NULL OR 
         CASE WHEN :include_subcategories THEN 
           t.category_id IN (
             WITH RECURSIVE subcategories AS (
               SELECT id FROM categories WHERE id = :category_id
               UNION
               SELECT c.id FROM categories c
               JOIN subcategories sc ON sc.id = c.parent_category_id
             )
             SELECT id FROM subcategories
           )
         ELSE 
           t.category_id = :category_id
         END
        )
    AND (:user_ids IS NULL OR p.user_id IN (SELECT unnest(string_to_array(:user_ids, ','))::int))
  GROUP BY t.id
) t
GROUP BY t.created_at
ORDER BY t.created_at

SQL Query Explanation

This report measures how quickly topics receive their first meaningful response by:

  • Calculating response time: For each topic, finds the first post that:
    • Is not from the original topic creator
    • Has a post number > 1 (not the initial post)
    • Is a regular post (post_type = 1)
    • Was created after the topic (positive time difference)
    • Hasn’t been deleted
  • Grouping by date: Aggregates these response times by the date the topic was created
  • Results show:
    • “date”: The date topics were created
    • “hours”: Average time (in hours) until the first response for topics created on that date
  • Exclusions:
    • Private messages are excluded
    • Deleted topics and posts are excluded
    • Self-replies are excluded

Parameters

  • :start_date (date) - format of YYYY-MM-DD
    • Starting date for the report period
    • Only topics created on or after this date will be included
  • :end_date (date) - format of YYYY-MM-DD
    • Ending date for the report period
    • Only topics created before this date will be included
  • :category_id (category_id, nullable)
    • When provided, filters results to a specific category
    • When null, includes topics from all categories
  • :include_subcategories (boolean) - Default: false
    • When true AND a category_id is provided, includes topics from all subcategories
    • When false, only includes topics from the exact specified category
  • :user_ids (user_id, nullable)
    • When provided, only includes responses from specific users
    • When null, includes responses from all users
    • Can accept multiple user IDs as a comma-separated list

Example Results

date 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 Mi Piace