I’m hoping to understand what % of new topics in a given category didn’t receive a response in the first week and what % were solved in the first week. Here’s my query:
-- [params] -- string :start_date = 2021-10-06 -- string :end_date = 2021-10-12 WITH solved_topics as (SELECT t.created_at::DATE, t.id, count(distinct case when u.action_type=15 and u.created_at < (t.created_at + INTERVAL '1 week') and u.target_topic_id=t.id then 1 ELSE NULL END) as solves, count(distinct case when p.created_at < (t.created_at + INTERVAL '1 week') and p.topic_id=t.id and p.post_number>1 then 1 ELSE NULL END) as responses from topics t join user_actions as u on u.target_topic_id = t.id join posts as p on p.topic_id = t.id where t.created_at BETWEEN :start_date AND :end_date AND t.user_id > 0 AND p.deleted_at is NULL AND t.category_id is NOT NULL group by t.created_at::DATE, t.id) SELECT st.created_at as date, count(distinct st.id) as "Number of New Topics", sum(st.solves)::INT as "# Solved within a week", round(sum(st.solves)/count(distinct st.id), 2) as "% Solved", (count(distinct st.id) - sum(st.responses))::INT as "Topics with No Response in First Week", round((count(distinct st.id) - sum(st.responses))/count(distinct st.id), 2) as "% with No Response in First Week" from solved_topics as st group by date order by date asc
I’m sure there are prettier ways to do this, but I’ve spot-checked a number of dates, and these numbers are consistent with what I’m seeing by looking at individual topics.
I would assume that for any day within the past week, the report found at
/admin/reports/topics_with_no_response should show the same number of topics with no response. In most cases it does; but in other cases it shows more topics with no response than I’m getting in my query.
Any ideas why this might be? Is there a way to see the query that drives the Topics with no response report so that I could compare/contrast?
Thanks in advance!