Replicando la consulta detrás de 'temas sin respuesta'

Datos e informes
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

Cuando comparé los temas del informe sin respuesta con un análisis de la lista de temas, noté que el número en el informe es mayor y concluí que cuenta como sin respuesta los temas que solo tienen respuestas del autor de la publicación original.
No puedo decir con certeza que esta sea la razón de la diferencia que notaste, porque no pude verificarlo ya que tu consulta no trae los números actuales, solo los números de “después de 1 semana”. Y no soy tan bueno en SQL.