محاكاة الاستعلام وراء 'المواضيع بلا ردود'

البيانات والتقارير
1

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:

-- [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!

2

عند مقارنة مواضيع التقرير التي لم يتم الرد عليها بتحليل قائمة المواضيع، لاحظت أن العدد في التقرير أكبر، واستنتجت أنه يُحتسب كمواضيع لم يتم الرد عليها المواضيع التي تحتوي فقط على ردود من مؤلف المنشور الأصلي.
لا يمكنني التأكيد على أن هذا هو السبب وراء الاختلاف الذي لاحظته، لأنني لم أتمكن من التحقق منه نظرًا لأن استعلامك لا يجلب الأرقام الحالية، بل الأرقام “بعد أسبوع واحد”. ولست جيدًا جدًا في لغة SQL.