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)
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
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
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))
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)
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.