This Data Explorer report provides a comprehensive analysis of solved and unsolved topics on a site, within a specified date range, and optionally filtered by a specific tag. :discourse: This report requires the Discourse Solved plugin to be enabled. This report is particularly useful for administrators and moderators looking to understand community responsiveness and to identify areas for improvement in user support and engagement. Solved and Unsolved Topic Stats with Date and Tag Parameters --[params] -- date :start_date = 2022-01-01 -- date :end_date = 2024-01-01 -- text :tag_name = all WITH valid_topics AS ( SELECT t.id, t.user_id, t.title, t.views, (SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count", t.created_at, (CURRENT_DATE::date - t.created_at::date) AS "total_days", STRING_AGG(tags.name, ', ') AS tag_names, -- Aggregate tags for each topic c.name AS category_name FROM topics t LEFT JOIN topic_tags tt ON tt.topic_id = t.id LEFT JOIN tags ON tags.id = tt.tag_id LEFT JOIN categories c ON c.id = t.category_id WHERE t.deleted_at IS NULL AND t.created_at::date BETWEEN :start_date AND :end_date AND t.archetype = 'regular' GROUP BY t.id, c.name ), solved_topics AS ( SELECT vt.id, dsst.created_at FROM discourse_solved_solved_topics dsst INNER JOIN valid_topics vt ON vt.id = dsst.topic_id ), last_reply AS ( SELECT p.topic_id, p.user_id FROM posts p INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p WHERE deleted_at IS NULL AND post_type = 1 GROUP BY topic_id) x ON x.post = p.id ), first_reply AS ( SELECT p.topic_id, p.user_id, p.created_at FROM posts p INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p WHERE deleted_at IS NULL AND post_type = 1 AND post_number > 1 GROUP BY topic_id) x ON x.post = p.id ) SELECT CASE WHEN st.id IS NOT NULL THEN 'solved' ELSE 'unsolved' END AS status, vt.tag_names, vt.category_name, vt.id AS topic_id, vt.user_id AS topic_user_id, ue.email, vt.title, vt.views, lr.user_id AS last_reply_user_id, ue2.email AS last_reply_user_email, vt.created_at::date AS topic_create, COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create, COALESCE(TO_CHAR(st.created_at, 'YYYY-MM-DD'), '') AS solution_create, COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)", COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)", COALESCE(st.created_at::date - vt.created_at::date, 0) AS "time_solution(days)", COALESCE(CEIL(EXTRACT(EPOCH FROM (st.created_at - vt.created_at)) / 3600.00), 0) AS "time_solution(hours)", vt.created_at::date, vt.posts_count AS number_of_replies, vt.total_days AS total_days_without_solution FROM valid_topics vt LEFT JOIN last_reply lr ON lr.topic_id = vt.id LEFT JOIN first_reply fr ON fr.topic_id = vt.id LEFT JOIN solved_topics st ON st.id = vt.id INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%') GROUP BY st.id, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, st.created_at, vt.posts_count, vt.total_days ORDER BY topic_create, vt.total_days DESC SQL Query Explanation The report is generated through a complex SQL query that utilizes Common Table Expressions (CTEs) to organize and process the data efficiently. The query is structured as follows: valid_topics: This CTE filters topics by the specified date range and archetype (‘regular’), excluding deleted topics. It also aggregates tags associated with each topic for later filtering by tag name if specified. solved_topics: Identifies topics that have been marked as solved. last_reply: Determines the user who made the last reply on each topic by finding the maximum post ID (indicating the most recent post) that is not deleted and is of post type 1 (indicating a regular post). first_reply: Similar to last_reply, but identifies the first user to reply to the topic after the original post. The main query then combines these CTEs to compile a detailed report on each topic, including whether it’s solved or unsolved, tag names, category name, topic and user IDs, emails, views, reply counts, and timings for the first reply and solution. Parameters start_date: The beginning of the date range for which to generate the report. end_date: The end of the date range for which to generate the report. tag_name: The specific tag to filter topics by. Use ‘all’ to include topics with any tag. Results The report provides the following information for each topic within the specified parameters: status: Indicates whether the topic has been solved or remains unsolved. tag_names: Shows the tags associated with the topic category_name: Shows the category associated with the topic topic_id: The unique identifier for the topic. topic_user_id: The ID of the user who created the topic. user_email: The email address of the topic creator. title: The title of the topic. views: The number of views the topic has received. last_reply_user_id: The ID of the user who made the last reply on the topic. last_reply_user_email: The email address of the user who made the last reply. topic_create: The date the topic was created. first_reply_create: The date of the first reply to the topic. solution_create: The date a solution was marked for the topic (if applicable). time_first_reply(days/hours): The time taken to receive the first reply, in days and hours. time_solution(days/hours): The time taken to solve the topic, in days and hours. created_at: The creation date of the topic. number_of_replies: The total number of replies to the topic. total_days_without_solution: The total number of days the topic has been active without a solution. Example Results status tag_names category_name topic_id topic_user_id user_email title views last_reply_user_id last_reply_user_email topic_create first_reply_create solution_create time_first_reply(days) time_first_reply(hours) time_solution(days) time_solution(hours) created_at number_of_replies total_days_without_solution solved support, password category1 101 1 user1@example.com How to reset my password? 150 3 user3@example.com 2022-01-05 2022-01-06 2022-01-07 1 24 2 48 2022-01-05 5 2 unsolved support, account category2 102 2 user2@example.com Issue with account activation 75 4 user4@example.com 2022-02-10 2022-02-12 2 48 0 0 2022-02-10 3 412 solved support category3 103 5 user5@example.com Can’t upload profile picture 200 6 user6@example.com 2022-03-15 2022-03-16 2022-03-18 1 24 3 72 2022-03-15 8 3 unsolved NULL category4 104 7 user7@example.com Error when posting 50 8 user8@example.com 2022-04-20 0 0 0 0 2022-04-20 0 373

Another awesome query, and another request from me. :slight_smile: Can you create a selection field to narrow down category/subcategory. I’d love to be able to run this report on just my tickets category.

Also, I found an odd edge case. You may or may not be able to account for it but no harm in asking. I have a topic that I created a reply to and marked it as a solution the day after it was posted. Then another tech gave a different answer and marked that one as the solution about 10 days later. The report shows time to solution as 1 day but total time without solution as 10 days. [PNG image]

Hi @tknospdr, To answer both your questions here: [תמונה] tknospdr: Can you create a selection field to narrow down category/subcategory. [תמונה] tknospdr: The report shows time to solution as 1 day but total time without solution as 10 days. You can use the below query to address this: --[params] -- date :start_date = 2022-01-01 -- date :end_date = 2024-01-01 -- text :tag_name = all -- null category_id :category_id WITH valid_topics AS ( SELECT t.id, t.user_id, t.title, t.views, (SELECT COUNT(*) FROM posts WHERE topic_id = t.id AND deleted_at IS NULL AND post_type = 1) - 1 AS "posts_count", t.created_at, (CURRENT_DATE::date - t.created_at::date) AS "total_days", STRING_AGG(tags.name, ', ') AS tag_names, c.name AS category_name, t.category_id FROM topics t LEFT JOIN topic_tags tt ON tt.topic_id = t.id LEFT JOIN tags ON tags.id = tt.tag_id LEFT JOIN categories c ON c.id = t.category_id WHERE t.deleted_at IS NULL AND t.created_at::date BETWEEN :start_date AND :end_date AND t.archetype = 'regular' GROUP BY t.id, c.name, t.category_id ), solved_topics AS ( SELECT dsst.topic_id, MIN(dsst.created_at) AS first_solution_at, -- Get earliest solution MAX(dsst.created_at) AS latest_solution_at -- Get latest solution FROM discourse_solved_solved_topics dsst GROUP BY dsst.topic_id ), last_reply AS ( SELECT p.topic_id, p.user_id FROM posts p INNER JOIN (SELECT topic_id, MAX(id) AS post FROM posts p WHERE deleted_at IS NULL AND post_type = 1 GROUP BY topic_id) x ON x.post = p.id ), first_reply AS ( SELECT p.topic_id, p.user_id, p.created_at FROM posts p INNER JOIN (SELECT topic_id, MIN(id) AS post FROM posts p WHERE deleted_at IS NULL AND post_type = 1 AND post_number > 1 GROUP BY topic_id) x ON x.post = p.id ) SELECT CASE WHEN st.topic_id IS NOT NULL THEN 'solved' ELSE 'unsolved' END AS status, vt.tag_names, vt.category_name, vt.id AS topic_id, vt.user_id AS topic_user_id, ue.email, vt.title, vt.views, lr.user_id AS last_reply_user_id, ue2.email AS last_reply_user_email, vt.created_at::date AS topic_create, COALESCE(TO_CHAR(fr.created_at, 'YYYY-MM-DD'), '') AS first_reply_create, COALESCE(TO_CHAR(st.first_solution_at, 'YYYY-MM-DD'), '') AS first_solution_create, COALESCE(TO_CHAR(st.latest_solution_at, 'YYYY-MM-DD'), '') AS latest_solution_create, COALESCE(fr.created_at::date - vt.created_at::date, 0) AS "time_first_reply(days)", COALESCE(CEIL(EXTRACT(EPOCH FROM (fr.created_at - vt.created_at)) / 3600.00), 0) AS "time_first_reply(hours)", COALESCE(st.first_solution_at::date - vt.created_at::date, 0) AS "time_to_first_solution(days)", COALESCE(CEIL(EXTRACT(EPOCH FROM (st.first_solution_at - vt.created_at)) / 3600.00), 0) AS "time_to_first_solution(hours)", COALESCE(st.latest_solution_at::date - vt.created_at::date, 0) AS "time_to_latest_solution(days)", COALESCE(CEIL(EXTRACT(EPOCH FROM (st.latest_solution_at - vt.created_at)) / 3600.00), 0) AS "time_to_latest_solution(hours)", vt.created_at::date, vt.posts_count AS number_of_replies, CASE WHEN st.topic_id IS NULL THEN vt.total_days ELSE COALESCE(st.latest_solution_at::date - vt.created_at::date, 0) END AS total_days_without_solution FROM valid_topics vt LEFT JOIN last_reply lr ON lr.topic_id = vt.id LEFT JOIN first_reply fr ON fr.topic_id = vt.id LEFT JOIN solved_topics st ON st.topic_id = vt.id INNER JOIN user_emails ue ON vt.user_id = ue.user_id AND ue."primary" = true LEFT JOIN user_emails ue2 ON lr.user_id = ue2.user_id AND ue2."primary" = true WHERE (:tag_name = 'all' OR vt.tag_names ILIKE '%' || :tag_name || '%') AND (:category_id ISNULL OR vt.category_id = :category_id) GROUP BY st.topic_id, st.first_solution_at, st.latest_solution_at, vt.tag_names, vt.category_name, vt.id, vt.user_id, ue.email, vt.title, vt.views, lr.user_id, ue2.email, vt.created_at, fr.created_at, vt.posts_count, vt.total_days ORDER BY topic_create, vt.total_days DESC Where the -- null category_id :category_id parameter can be used to (optionally) select a category to run the report for, and the results track both the first and latest solutions. Additionally, the the total_days_without_solution result will now use the latest solution date instead of the first one.

סטטיסטיקות נושאים פתורים ובלתי פתורים עם פרמטרים של תאריך ותגיות

קהילה נתונים ודיווח

tknospdr (David Muszynski) 19 בספטמבר,‏ 2025,‏ 8:22pm 5

מעולה, תודה! נראה מצוין.

נושא	תגובות	צפיות
Topics stats of Solved/Unsolved topics, inc. 'time to solution' Data & reporting solved , sql-query	262	22 באפריל,‏ 2023
Solved Topic and Moderator Response Stats by Category Data & reporting solved , sql-query	459	3 באפריל,‏ 2024
Topics solved by regular users (lists topics) Data & reporting solved , sql-query	223	12 בפברואר,‏ 2018
Topics solved by staff (lists topics) Data & reporting solved , sql-query	228	12 בפברואר,‏ 2018
Top 50 active topics (and their associated categories) Data & reporting sql-query	197	12 בפברואר,‏ 2018

סטטיסטיקות נושאים פתורים ובלתי פתורים עם פרמטרים של תאריך ותגיות

נושאים קשורים