3
$\begingroup$

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

enter image description here

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?

| |
$\endgroup$
  • 2
    $\begingroup$ To avoid confusion it could be useful to be precise in what you want to know, and keep the things apart. There is a the number of questions that "need review" that is the number of questions awaiting review right now; there is the "number of reviews" which is the number of review tasks carried out (which can range from 1 to 5 per question). and then there would be the "number of questions actually closed" and "the number of questions that got at least one vote/flag to close". It seems you blur the distinction between the first and the second. $\endgroup$ – quid Aug 10 at 12:07
  • $\begingroup$ Thanks for pointing this out. Regarding your comment, I only want to know the first thing you mentioned: the number of questions that "need review". $\endgroup$ – Toby Mak Aug 10 at 12:08
  • 1
    $\begingroup$ Thank you for the clarification; then I would recommend to update the question and the answer as "tracks the number of close votes over the past few months" has nothing to do with the size of the "need review" number. $\endgroup$ – quid Aug 10 at 12:10
  • $\begingroup$ I see that for answer it is already done, but for the question it is not yet done. $\endgroup$ – quid Aug 10 at 12:12
  • $\begingroup$ I'm busy writing a request for reopening another question. I'll come back to this in a while. $\endgroup$ – Toby Mak Aug 10 at 12:13
  • 2
    $\begingroup$ Alright, but the edit just now augmented the confusion as you insist on the number of votes now. To be clear "need review" would be analogue to the length of the queue waiting in front of a ticket counter; it is not in itself related to the number of customers served and the number of actions by people working at the ticket counter. $\endgroup$ – quid Aug 10 at 12:14
  • $\begingroup$ I have now edited both the question and the answer to clarify. Is this better? $\endgroup$ – Toby Mak Aug 10 at 12:21
  • 2
    $\begingroup$ Yes thank you it is more clear now. $\endgroup$ – quid Aug 10 at 12:23
  • $\begingroup$ Its at 1 need review now! (Shouldn't it be 1 needs review?) $\endgroup$ – Calvin Khor Aug 12 at 9:13
  • $\begingroup$ @CalvinKhor It was at $0$ needs review before. $\endgroup$ – Toby Mak Aug 12 at 9:31
3
$\begingroup$

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:

Graph showing 4 series, starting at Jan 1st 2020, the number of tasks peaks at 600 early may, together with number of reviews. After July a strong drop to 170 of number of tasks while number of reviews show a strong growth. Hours to completion has a simgle peak at the end of june

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.

| |
$\endgroup$
2
$\begingroup$

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.

| |
$\endgroup$
  • 3
    $\begingroup$ "From what I can tell, the number of close votes recently stands at its lowest in 3 years." If you look at the size of the queue, which is what the first query seems to do, that does not in itself tell much about the number of votes. The queue is much shorter than it was; this does not necessarily mean that there are less votes. The second query is more relevant for this (the last month is ongoing and thus needs to be discarded). $\endgroup$ – quid Aug 10 at 11:58
  • $\begingroup$ My bad, it seems that I have confused the two concepts with each other. I have edited both the question and my answer to clarify. $\endgroup$ – Toby Mak Aug 10 at 12:01
  • 3
    $\begingroup$ Next on the agenda: fit a model and forecast the size of the review queue next week? $\endgroup$ – Alexander Gruber Aug 10 at 15:55
  • $\begingroup$ Probably not. I'm sure there are too many factors to consider if the model is going to be even slightly accurate. $\endgroup$ – Toby Mak Aug 10 at 23:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .