Replicare la query dietro 'argomenti senza risposta'

Dati e reportistica
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

Quando ho confrontato gli argomenti del report senza risposta con un’analisi dell’elenco degli argomenti, ho notato che il numero nel report è maggiore e ho concluso che conta come argomenti senza risposta quelli che hanno solo risposte dall’autore del post originale.
Non posso dire con certezza che questo sia il motivo della differenza che hai notato, perché non ho potuto verificarlo poiché la tua query non fornisce i numeri attuali, ma solo i numeri “dopo 1 settimana”. E non sono così bravo in SQL.