# Replicating the query behind 'topics with no response'
**URL:** https://meta.discourse.org/t/replicating-the-query-behind-topics-with-no-response/206062
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [October 14, 2021, 3:21pm UTC](https://meta.discourse.org/t/replicating-the-query-behind-topics-with-no-response/206062 "2021-10-14T15:21:56Z")
**Posts on this page:** 2
**Page:** 1
### Author:  [@mbcrosier](https://meta.discourse.org/u/mbcrosier)
#### Post date: [October 14, 2021, 3:21pm UTC](https://meta.discourse.org/t/replicating-the-query-behind-topics-with-no-response/206062/1 "2021-10-14T15:21:56Z")
Hi there!
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:
```plaintext
-- [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!
---
### Author:  [@camilaoliveira](https://meta.discourse.org/u/camilaoliveira)
#### Post date: [March 1, 2023, 4:40pm UTC](https://meta.discourse.org/t/replicating-the-query-behind-topics-with-no-response/206062/2 "2023-03-01T16:40:29Z")
When I compared the report topics with no response with a analysis of the list of topics, I noted the number in the report is bigger, and I concluded it counts as with no responde the topics that only have replies from the original post author.
I can’t say for sure this is the reason behind the difference you noticed, because I couldn’t check since your query doesn’t bring the current numbers, just the “after 1 week” numbers. And I’m not that good in SQL.