Dashboard Report - Topics with No Response

This is an SQL version of the Dashboard Report for Topics with No Response.

The dashboard report is designed to count the number of topics created within a specified date range that have not received any responses from other users. This report can be filtered by a specific category and can optionally include subcategories.

-- [params]
-- date :start_date = 2024-01-01    
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false

WITH no_response_total AS (
    FROM (
      SELECT t.id, t.created_at, MIN(p.post_number) first_reply
      FROM topics t
      LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
      WHERE t.archetype <> 'private_message'
      AND t.deleted_at ISNULL
      AND (
        :category_id IS NULL
        OR t.category_id = :category_id
        OR (
          AND t.category_id IN (
            SELECT id FROM categories WHERE parent_category_id = :category_id
      GROUP BY t.id
    ) tt
    WHERE tt.first_reply IS NULL OR tt.first_reply < 2

  DATE(nrt.created_at) AS date, 
  COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date


  • Date 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.
  • Category Parameters:
    • :category_id: An integer parameter that can be set to the ID of a specific category to narrow down the analysis to posts within that category. If it’s set to null or not provided, topics from all categories are considered.
    • :include_subcategories: A boolean parameter that controls whether to include posts from subcategories of the specified :category_id. If set to true, the report will include links to posts in both the specified category and its subcategories; if false, only the specified category will be considered.

SQL Query Explanation

The query starts with a Common Table Expression (CTE) named no_response_total. This CTE performs the following steps:

  • Selection of Topics: It selects all topics (t.id) and their creation dates (t.created_at) from the topics table.
  • Left Join with Posts: It performs a left join with the posts table to find the first reply to each topic. The join conditions ensure that the post is not by the topic creator (p.user_id != t.user_id), the post has not been deleted (p.deleted_at IS NULL), and the post is of type 1, which typically represents a standard reply.
  • Filtering Topics: The query filters out topics that are private messages (t.archetype <> 'private_message') and topics that have been deleted (t.deleted_at ISNULL).
  • Category Filtering: If a :category_id is provided, the query will filter topics to include only those in the specified category. If :include_subcategories is true, it will also include topics from subcategories of the specified category.
  • Grouping and Minimum Post Number: The topics are grouped by their ID, and the minimum post number (MIN(p.post_number)) is calculated to find the first reply.
  • Filtering for No Response: The subquery tt filters out topics that have a first reply with a post number greater than or equal to 2, leaving only topics with no replies (tt.first_reply IS NULL) or only the original post (tt.first_reply < 2).

After the no_response_total CTE is defined, the main query does the following:

  • Filter by Date Range: It filters the topics from the CTE by the provided start and end dates (:start_date and :end_date).
  • Counting Topics Without Response: It counts the number of topics without a response for each date within the specified range.
  • Grouping by Date: The results are grouped by the date of topic creation (DATE(nrt.created_at)).
  • Ordering: The results are ordered by date in ascending order.

Example Results

date topics_without_response
2024-01-02 4
2024-01-03 8
2024-01-04 4
2024-01-05 3
2024-01-06 3
1 Like