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.
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
Yes, here’s an updated version of the query that looks back 7 days from the current date without using parameters.
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
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
I’m really struggling to be able to turn this into not date driven, but month year.
I’ve done multiple things to try and make this work, but it keeps telling me that the column doesn’t exist (when it does as I’ve just created it within a with statement, and then subsequently am referencing it)
How would someone amend this code so instead of looking at topics with no response day by day, we would be able to see it Year by Year, Month by month etc.
To modify the query so that it can aggregate topics without responses by year, month, or other time intervals, you could add a parameter to specify the desired interval on the date_trunc function to achieve this.
For example:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options: day, week, month, year
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_trunc(:interval, nrt.created_at)::date AS period,
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 period
ORDER BY period ASC
If you wanted to remove the parameters, you could alternatively use a query like:
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
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
Thanks, that’s resolved the how to get the year piece.
I’m now stuck again as dates in postgre seem to behave differently
to_char(t.created_at, ‘MM-YY’) as Yearmonth,
This is giving me 10-22 which represents “Oct-22”
How am I able to change 10-22 to Oct-22? I have tried to look at guidance within discourse but not been able to find this, unless I’m not sure where to look?
To change the date format from 10-22 to Oct-22 in PostgreSQL, you can use the TO_CHAR function. This function allows you to format dates in various ways, for example:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
In this SQL statement:
TO_DATE('10-22', 'MM-YY') converts the string 10-22 into a date type using the format MM-YY.
TO_CHAR(..., 'Mon-YY') then formats this date to display the abbreviated month name followed by the year, resulting in Oct-22.
Here’s another example of this based off of the version of the Topics with No Response query with the interval parameter shared above:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
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
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
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 period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
For reference, the results for this query would look like:
