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 ofYYYY-MM-DD
.
- The query accepts two parameters,
- 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 thetopics
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 |