Data explorer- calculation of unanswered topics

Hi,

I would like your views and your help on a query I’m writing using DATA explorer plugin .

The purpose of this query is to calculate by week, the number of topics where the user is waiting for an answer for at least 3 days.

The user is either the owner of the topic , either another user who does not belongs to the dedicated support teams (for example another user who add a question).

The calculation should be done starting at the first January , and every Thursday at 00:00 PM, whenever the query is run during the week .

Each week , the query is run, and the calculation is done .

My main problem, is that the calculation for the past week , could be changed ,due to activities on old topics.

I try to consider every case to prevent from having this.

Furthermore, it seems the way I used to determine the starting point ( Thursday 00:00 PM) is not correct. The result could change for the current week, depending on when the query is run .
here is my query:

--[params]
--int :noreplyday = 3
-- string :date = 2018-01-01
--int :lastpostinmonth = 6
-- int :createdatetopicinmonth = 12
with intervals as (
    select
        :date::date as start_time, 
        n as end_time
    from generate_series(:date::date+(4- EXTRACT(DOW FROM :date::date  ))* (INTERVAL '1 days') ,
                          CURRENT_DATE+(4- EXTRACT(DOW FROM CURRENT_TIMESTAMP ))* (INTERVAL '1 days'),
                          INTERVAL '1 week') n
),

sys as (
SELECT  id
    FROM users AS u
    where u.username='system'
    OR u.admin = 't'
    OR u.moderator = 't'
    ),

S1 as (
  select  user_id
  from user_emails u
  where u.primary = true
  and u.email LIKE '%Support1%'
),
S2 as (
  select  user_id
  from user_emails u
  where u.primary = true
  and u.email LIKE '%Support2'
),

exclude_user_id as (
select users.id
from users
where  id  IN (SELECT * FROM sys) -- to exclude system message and announcement from staff
or id  IN (SELECT * FROM S1) -- to exclude   Support team 1
or id  IN (SELECT * FROM S2) -- to exclude   Support team 2
),

tUA as(
SELECT   t.id
    FROM topics t 
INNER JOIN user_actions ua ON t.id=ua.target_topic_id 
WHERE ua.action_type=15 ),
 
tsolved as (
SELECT t.id, t.updated_at
FROM topics t 
INNER JOIN tUA on tUA.id=t.id
AND t.created_at >= :date
AND t.archetype != 'private_message'
AND t.archetype != 'banner'
 ), 
 
Unanswerowner as (    
SELECT t.id,  t.created_at ,t.last_posted_at
FROM topics t, categories cat
WHERE t.id NOT IN (SELECT * FROM tUA) -- to exclude solved issue
AND t.user_id NOT IN (SELECT * FROM sys) -- to exclude system message and announcement from staff
AND t.user_id NOT IN (SELECT * FROM S2) -- to exclude  topics created by support team 2
AND t.user_id=t.last_post_user_id --owner added information
AND t.posts_count<> 0 -- topics deleted
AND age(t.created_at)>= (:noreplyday) * (INTERVAL '1 days')
AND t.created_at >= :date
AND t.archetype != 'private_message'
AND t.archetype != 'banner'
AND t.category_id=cat.id
and t.category_id!=7
AND t.closed!= true
GROUP BY t.id,  t.created_at
ORDER BY t.created_at DESC
),

Unansweruser as (    
SELECT t.id,  t.created_at , t.last_posted_at,t.last_post_user_id
FROM topics t , categories cat
WHERE t.id NOT IN (SELECT * FROM tUA) -- to exclude solved issue
AND t.last_post_user_id NOT IN (SELECT * FROM sys)
AND t.user_id!=t.last_post_user_id --user added information
AND t.posts_count<> 0 -- topics deleted
AND t.created_at >= :date
AND t.archetype != 'private_message'
AND t.archetype != 'banner'
AND t.category_id=cat.id
and t.category_id!=7
AND t.closed!= true
GROUP BY t.id,  t.created_at
ORDER BY t.created_at DESC
)



SELECT  
        SUM( CASE WHEN t.created_at >= i.start_time::timestamp THEN 1 ELSE 0 END) as  totaltopic,
        SUM(CASE WHEN (Unanswerowner.id=t.id  AND age(Unanswerowner.last_posted_at)>= (:noreplyday) * (INTERVAL '1 days') and Unanswerowner.last_posted_at <i.end_time) THEN 1 ELSE 0 END) 
   + SUM(CASE WHEN (Unansweruser.id=t.id and Unansweruser.last_posted_at< i.end_time and Unansweruser.last_post_user_id not IN (SELECT * FROM S1)and Unansweruser.last_post_user_id not IN (SELECT * FROM S2) AND age(Unansweruser.last_posted_at)>= (:noreplyday) * (INTERVAL '1 days')) THEN 1 ELSE 0 END) 
   +SUM (CASE WHEN (Unansweruser.id=t.id and Unansweruser.last_post_user_id  IN (SELECT * FROM S1) and Unansweruser.last_posted_at>= i.end_time ) THEN 1 ELSE 0 END) 
+ SUM (CASE WHEN (Unansweruser.id=t.id and Unansweruser.last_post_user_id  IN (SELECT * FROM S2) and Unansweruser.last_posted_at>= i.end_time ) THEN 1 ELSE 0 END) 
+ SUM (CASE WHEN (tsolved.id=t.id  and tsolved.updated_at>= i.end_time   and age(tsolved.updated_at,t.created_at )>= (:noreplyday) * (INTERVAL '1 days') ) THEN 1 ELSE 0 END) as Unansweredtotal,

        
        i.start_time::date as starttime,
        i.end_time as endtime,
        EXTRACT(WEEK FROM i.end_time::date) as week,
        CURRENT_DATE - i.end_time as time_ago
FROM topics t
full outer join Unanswerowner on Unanswerowner.id=t.id
full outer join Unansweruser on Unansweruser.id=t.id
left join tsolved on tsolved.id=t.id
right join intervals i
on  t.created_at < i.end_time
AND t.user_id NOT IN (SELECT * FROM sys) -- to exclude system message and announcement from staff
AND t.user_id NOT IN (SELECT * FROM S2) -- to exclude  topics created by support team 2
AND t.archetype != 'private_message'
AND t.archetype != 'banner'
AND t.closed!= true
AND t.posts_count<> 0 -- topics deleted
AND t.category_id!=7
group by i.start_time, i.end_time--,t.title, t.id,t.user_id,t.category_id, t.created_at,t.last_posted_at
 
 order by i.end_time DESC

thanks for your help!