Dashboard Report - Topics with No Response

data & reporting
, ,
1

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 (
SELECT *
    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 (
          :include_subcategories
          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
)

SELECT 
  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
GROUP BY date
ORDER BY date ASC

Parameters

  • 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
2

Are you able to make a version that does not contain parameters? I want to create a version which looks back 7 days to be then sent via email to people and struggling to use this code as there are parameters set in it

Thanks

1 Like
3

Yes, here’s an updated version of the query that looks back 7 days from the current date without using parameters. :slightly_smiling_face:

This version does not include any filtering by categories or subcategories.

WITH no_response_total AS (
  SELECT *
  FROM (
    SELECT 
      t.id, 
      t.created_at, 
      MIN(p.post_number) AS 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 IS NULL
      AND (
        t.category_id = :category_id
        OR 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
)

SELECT 
  DATE(nrt.created_at) AS date, 
  COUNT(nrt.id) AS topics_without_response
FROM 
  no_response_total nrt
WHERE 
  nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY 
  date
ORDER BY 
  date ASC

If you wanted to adjust how far back the query looks, you would just need to change this line in the query:

nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
4

Thanks for the response, I’ll come back to this when I next need it as the focus has now shifted to something else so not had the time to come back to this

1 Like