「返信のないトピック」の背後にあるクエリの複製

コミュニティ データとレポート
1

こんにちは！

特定のカテゴリで、最初の1週間以内に回答が得られなかった新しいトピックの割合と、同じ期間内に解決されたトピックの割合を理解したいと考えています。以下が私のクエリです：

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

もっと綺麗な方法があるかもしれませんが、いくつかの日付をスポットチェックした結果、これらの数値は個別のトピックを確認して得た結果と一致しています。

過去1週間内の任意の日付について、/admin/reports/topics_with_no_response で見つかるレポートには、回答がなかったトピック数が同じ表示になるはずです。多くの場合は一致しますが、場合によっては、私のクエリで得られる数値よりも多くの「回答なし」トピックが表示されています。

これの原因について何かご存知でしょうか？「回答なしトピック」レポートを駆動しているクエリを確認する方法があれば、比較検討できるのですが。

よろしくお願いいたします！

2

レポートのトピックと返信がない場合の比較を、トピックリストの分析と比較したところ、レポートの数値の方が大きいことがわかりました。これは、返信がないと見なされるトピックには、元の投稿者からの返信のみが含まれていると結論付けました。

あなたのクエリは現在の数値ではなく、「1週間後」の数値しか表示しないため、確認できませんでした。また、SQLが得意ではないため、これがあなたが気づいた違いの背後にある理由であるとは断言できません。