Is there a functioning tracker for the number of questions that need review in the close queue?

Question

Recently, the number of items that need review in the close queue stood at $10$, nearly into the single digits. Here is a screenshot:

I was curious and searched for a way to track the number of questions awaiting review over the past few months, or for that matter, any other moderation queue (including the queues for reopen votes, suggested edits, low-quality posts, etc). I found this post, this post and this other post, but when I clicked on the links, Google Drive showed me a message saying that the file has been deleted, and I also got a 404 error message. The answers were posted several years ago, and the users have since deleted their accounts, so there is no way to contact them.

Is there a functioning, up-to-date tracker for the number of questions awaiting review on Maths SE, over the past few months? Or is there any way I can use the Stack Exchange data explorer to query this information?

Answer 1

I've iterated on the SEDE queries that were already there and combined that into this query

I've added three new data series: number of review tasks not completed, number of reviews (it takes a couple of reviews to complete one review task) and the average time in hours it took for the the task to be completed (I've added a + 50 offset to have the plot in the free space I had in that graph).

Also added is a selection option, so you select the start date from where you want to graph to start. The end date of the graph is now up to last Sunday. On Sunday the data is refreshed at 03:00 UTC so that day always has skewed results.

Combining all this goodness gives you this graph:

Here is the query itself for anyone interested.

declare @closetasktypeid int = 2
declare @maxdates int
declare @start date
select @start = (select min(creationdate) 
                 from reviewtasks 
                 where reviewtasktypeid = @closetasktypeid);
select @maxdates = datediff(d, @start, convert(date,dateadd(d, -(datepart(dw, getdate())), getdate())))


create table #closevotes (creationdate date, deletiondate date);
create index #cv_date on #closevotes(creationdate, deletiondate);
insert into #closevotes 
select creationdate
,  deletiondate
from reviewtasks 
where reviewtasktypeid = @closetasktypeid -- close
and creationdate > ##startdate:string?2017-07-10##

;with days as (
select top (@maxdates)
row_number() over(order by id) as rownum
from posts 
)

select dateadd(d, rownum, @start) as utcdate
     , count(*) as [# of tasks] 
     , sum(case when r.deletiondate is null then 1 else 0 end) [not completed]
     , (select count(*)
             from reviewtaskresults rtr
             inner join reviewtasks rt on rt.id = rtr.reviewtaskid 
             where rt.reviewtasktypeid = @closetasktypeid
             and convert(date,rtr.creationdate) = dateadd(d, rownum, @start)
             ) [# of reviews] 
     , (select 50 + avg(datediff(hh,creationdate, deletiondate))
        from reviewtasks
        where convert(date,deletiondate) = dateadd(d, rownum, @start)) [# hours to complete (offset + 50)]
from days d
inner join #closevotes r 
        on dateadd(d,rownum, @start) 
           between r.creationdate 
           and coalesce(r.deletiondate, getdate())
group by rownum
order by rownum

Keep in mind SEDE is updated once a week on Sunday.
The respectable Monica Cellio wrote the awesome SEDE Tutorial.
Say "Hi" in SEDE chat.

Answer 2

I've found my answer on the Stack Exchange data explorer. This query is up-to-date and shows the last few months' worth of data (in fact, there are more than $3$ years of data in total). The query updates the number of questions awaiting review once per day at midnight UTC time, which is just what I need.

As far as I can tell, the number of questions that need review in the close queue recently stands at its lowest in 3 years, when measured once per day. This is a far cry from May of 2020, where there were once $592$ posts that needed review.

For anyone that is interested in the number of review tasks carried out, this query has the number of reviews completed per month, and is also up-to-date (as of August 2020). The query updates at the end of every month.