The following reports require the Discourse Solved plugin to be enabled.
The following Data Explorer queries are designed to analyze topics within each category on a site, focusing on metrics related to solving topics and moderator responses to topics. They operate within a specified date range and optionally filter by category name. These queries assume that topics in all categories can be solved.
Admins can use these reports to understand how effectively topics are being solved within different categories, and identify areas where moderator response times can be improved. By understanding the dynamics of topic resolution in parent and subcategories, admins can make informed decisions to improve community engagement and support.
Both queries have the same parameters, and very similar results.
Parameters
:start_date
and:end_date
: Define the date range for the analysis.:category_name
: Optionally filters the results to a specific category.
Results
date
/date_range
: The specific date or the start of the period for which the data is reported, and specifies the granularity of the time period covered by each row of data (month).parent_category_name
/category_name
: The name of the category or specific subcategory to which the data pertains.total_number_of_topics
: Total number of topics created in the specified category or subcategory within the date range.number_of_solved_topics
: Total number of topics that were marked as solved within the specified category or subcategory and date range.avg_time_to_solve_hours
: Average time taken to mark a solved topic as solved, measured in hours.percent_solved_under_24h
: Percentage of topics that were solved within 24 hours of being posted (excludes topics that have not been solved).avg_solved_topics_moderator_time_to_first_response_hours
: Average time it took for a moderator to respond to topics that were eventually marked as solved, measured in hours.avg_time_to_first_response_hours
: Average time taken for the first response to be posted in a topic, measured in hours.percent_all_topics_with_moderator_responses_under_24h
: Percentage of all topics that received a moderator response within 24 hours of being posted.
Solved Topic and Moderator Response Stats by Category
This query groups results by main (parent) categories.
--[params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01
-- null string :category_name
WITH solved_topics AS (
SELECT
t.category_id,
t.id AS topic_id,
DATE_TRUNC('month', t.created_at) AS month,
MIN(p.created_at) FILTER (WHERE p.id = ua.target_post_id) AS solution_posted_at,
MIN(p.created_at) FILTER (WHERE p.user_id = u.id AND u.moderator AND p.post_number > 1) AS first_moderator_response
FROM topics t
JOIN posts p ON t.id = p.topic_id
JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.target_post_id = p.id
JOIN users u ON p.user_id = u.id
WHERE t.archetype = 'regular'
AND ua.action_type = 15 -- action_type for 'accepted solution'
GROUP BY t.category_id, t.id, month
),
moderator_responses AS (
SELECT
topic_id,
MIN(created_at) AS first_response
FROM posts
WHERE user_id IN (SELECT id FROM users WHERE moderator)
AND post_number > 1
GROUP BY topic_id
),
moderator_response_stats AS (
SELECT
pc.id AS parent_category_id,
DATE_TRUNC('month', t.created_at) AS month,
COUNT(*) FILTER (WHERE EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600 < 24) AS topics_with_first_response_under_24h,
COUNT(*) AS total_topics
FROM topics t
JOIN categories c ON t.category_id = c.id
LEFT JOIN categories pc ON c.parent_category_id = pc.id OR c.id = pc.id -- Includes top-level categories as their own parents
LEFT JOIN moderator_responses mr ON t.id = mr.topic_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY pc.id, month
),
total_topics_per_category AS (
SELECT
pc.id AS parent_category_id,
DATE_TRUNC('month', t.created_at) AS month,
COUNT(*) AS total_number_of_topics
FROM topics t
JOIN categories c ON t.category_id = c.id
LEFT JOIN categories pc ON c.parent_category_id = pc.id OR c.id = pc.id -- Includes top-level categories as their own parents
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY pc.id, month
),
category_hierarchy AS (
SELECT
c.id AS category_id,
c.name AS category_name,
COALESCE(pc.id, c.id) AS parent_category_id, -- Use the category itself if it has no parent
COALESCE(pc.name, c.name) AS parent_category_name
FROM categories c
LEFT JOIN categories pc ON c.parent_category_id = pc.id
),
solved_stats AS (
SELECT
st.month,
ch.parent_category_id,
COUNT(*) AS number_of_solved_topics,
AVG(EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600) AS avg_time_to_solve_hours,
AVG(CASE WHEN EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600 < 24 THEN 1 ELSE 0 END) AS percent_solved_under_24h,
AVG(EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600) AS avg_time_to_first_response_hours,
AVG(EXTRACT(EPOCH FROM (st.first_moderator_response - t.created_at))/3600) AS solved_topics_avg_time_to_first_response_hours,
COALESCE(mrs.topics_with_first_response_under_24h::FLOAT / NULLIF(mrs.total_topics, 0), 0) AS percent_moderator_responses_under_24h
FROM solved_topics st
JOIN topics t ON st.topic_id = t.id
JOIN category_hierarchy ch ON t.category_id = ch.category_id
LEFT JOIN moderator_responses mr ON st.topic_id = mr.topic_id
LEFT JOIN moderator_response_stats mrs ON ch.parent_category_id = mrs.parent_category_id AND DATE_TRUNC('month', t.created_at) = mrs.month
WHERE t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY st.month, ch.parent_category_id, mrs.topics_with_first_response_under_24h, mrs.total_topics
)
SELECT
st.month::date AS date,
'month' AS date_range,
ch.parent_category_name,
ttpc.total_number_of_topics,
st.number_of_solved_topics,
ROUND(st.avg_time_to_solve_hours::numeric,2) AS avg_time_to_solve_hours,
ROUND((st.percent_solved_under_24h * 100)::numeric, 2) AS percent_solved_under_24h,
ROUND(st.solved_topics_avg_time_to_first_response_hours::numeric,2) AS avg_solved_topics_moderator_time_to_first_response_hours,
ROUND(st.avg_time_to_first_response_hours::numeric,2) AS avg_time_to_first_response_hours,
ROUND((st.percent_moderator_responses_under_24h * 100)::numeric, 2) AS percent_all_topics_with_moderator_responses_under_24h
FROM solved_stats st
JOIN category_hierarchy ch ON st.parent_category_id = ch.parent_category_id
LEFT JOIN total_topics_per_category ttpc ON st.parent_category_id = ttpc.parent_category_id AND st.month = ttpc.month
WHERE (:category_name IS NULL OR ch.parent_category_name = :category_name)
GROUP BY date,st.month,ch.parent_category_name,ttpc.total_number_of_topics,st.number_of_solved_topics,st.avg_time_to_solve_hours,st.percent_solved_under_24h,st.solved_topics_avg_time_to_first_response_hours,st.avg_time_to_first_response_hours,st.percent_moderator_responses_under_24h
ORDER BY st.month, ch.parent_category_name
Example Results
date | date_range | parent_category_name | total_number_of_topics | number_of_solved_topics | avg_time_to_solve_hours | percent_solved_under_24h | avg_solved_topics_moderator_time_to_first_response_hours | avg_time_to_first_response_hours | percent_all_topics_with_moderator_responses_under_24h |
---|---|---|---|---|---|---|---|---|---|
2023-01-01 | month | General Discussion | 150 | 100 | 12.5 | 75.00 | 5.00 | 6.00 | 80.00 |
Solved Topic and Moderator Response Stats by Sub-Category
This query groups results by sub-categories.
--[params]
-- date :start_date = 2023-01-01
-- date :end_date = 2025-01-01
-- null string :category_name
WITH solved_topics AS (
SELECT
t.category_id,
t.id AS topic_id,
DATE_TRUNC('month', t.created_at) AS month,
MIN(p.created_at) FILTER (WHERE p.id = ua.target_post_id) AS solution_posted_at,
MIN(p.created_at) FILTER (WHERE p.user_id = u.id AND u.moderator AND p.post_number > 1) AS first_moderator_response
FROM topics t
JOIN posts p ON t.id = p.topic_id
JOIN user_actions ua ON ua.target_topic_id = t.id AND ua.target_post_id = p.id
JOIN users u ON p.user_id = u.id
WHERE t.archetype = 'regular'
AND ua.action_type = 15 -- action_type for 'accepted solution'
GROUP BY t.category_id, t.id, month
),
moderator_responses AS (
SELECT
topic_id,
MIN(created_at) AS first_response
FROM posts
WHERE user_id IN (SELECT id FROM users WHERE moderator)
AND post_number > 1
GROUP BY topic_id
),
moderator_response_stats AS (
SELECT
t.category_id,
DATE_TRUNC('month', t.created_at) AS month,
COUNT(*) FILTER (WHERE EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600 < 24) AS topics_with_first_response_under_24h,
COUNT(*) AS total_topics
FROM topics t
LEFT JOIN moderator_responses mr ON t.id = mr.topic_id
WHERE t.archetype = 'regular'
AND t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY t.category_id, month
),
total_topics_per_category AS (
SELECT
category_id,
DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS total_number_of_topics
FROM topics
WHERE archetype = 'regular'
AND created_at >= :start_date
AND created_at <= :end_date
GROUP BY category_id, month
),
solved_stats AS (
SELECT
st.month,
st.category_id,
COUNT(*) AS number_of_solved_topics,
AVG(EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600) AS avg_time_to_solve_hours,
AVG(CASE WHEN EXTRACT(EPOCH FROM (st.solution_posted_at - t.created_at))/3600 < 24 THEN 1 ELSE 0 END) AS percent_solved_under_24h,
AVG(EXTRACT(EPOCH FROM (mr.first_response - t.created_at))/3600) AS avg_time_to_first_response_hours,
AVG(EXTRACT(EPOCH FROM (st.first_moderator_response - t.created_at))/3600) AS solved_topics_avg_time_to_first_response_hours,
COALESCE(mrs.topics_with_first_response_under_24h::FLOAT / NULLIF(mrs.total_topics, 0), 0) AS percent_moderator_responses_under_24h
FROM solved_topics st
JOIN topics t ON st.topic_id = t.id
LEFT JOIN moderator_responses mr ON st.topic_id = mr.topic_id
LEFT JOIN moderator_response_stats mrs ON t.category_id = mrs.category_id AND DATE_TRUNC('month', t.created_at) = mrs.month
WHERE t.created_at >= :start_date
AND t.created_at <= :end_date
GROUP BY st.month, st.category_id, mrs.topics_with_first_response_under_24h, mrs.total_topics
),
category_hierarchy AS (
SELECT
c.id AS category_id,
c.name AS category_name,
COALESCE(pc.name, c.name) AS parent_category_name
FROM categories c
LEFT JOIN categories pc ON c.parent_category_id = pc.id
)
SELECT
st.month::date AS date,
'month' AS date_range,
ch.parent_category_name,
ch.category_name,
ttpc.total_number_of_topics,
st.number_of_solved_topics,
ROUND(st.avg_time_to_solve_hours::numeric,2) AS avg_time_to_solve_hours,
ROUND((st.percent_solved_under_24h * 100)::numeric, 2) AS percent_solved_under_24h,
ROUND(st.solved_topics_avg_time_to_first_response_hours::numeric,2) AS avg_solved_topics_moderator_time_to_first_response_hours,
ROUND(st.avg_time_to_first_response_hours::numeric,2) AS avg_all_topics_moderator_time_to_first_response_hours,
ROUND((st.percent_moderator_responses_under_24h * 100)::numeric, 2) AS percent_all_topics_with_moderator_responses_under_24h
FROM solved_stats st
JOIN category_hierarchy ch ON st.category_id = ch.category_id
LEFT JOIN total_topics_per_category ttpc ON st.category_id = ttpc.category_id AND st.month = ttpc.month
WHERE (:category_name IS NULL OR ch.parent_category_name = :category_name OR ch.category_name = :category_name)
ORDER BY st.month, ch.parent_category_name, ch.category_name
Example Results
date | date_range | parent_category_name | category_name | total_number_of_topics | number_of_solved_topics | avg_time_to_solve_hours | percent_solved_under_24h | avg_solved_topics_moderator_time_to_first_response_hours | avg_all_topics_moderator_time_to_first_response_hours | percent_all_topics_with_moderator_responses_under_24h |
---|---|---|---|---|---|---|---|---|---|---|
2023-01-01 | month | General Discussion | Feedback | 50 | 30 | 8.00 | 80.00 | 4.00 | 5.00 | 85.00 |