Rise and fall of the Close Vote Queue (only latest items)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow на русском Meta

Вопросы и ответы о сайте для программистов

declare @maxdates int
declare @start date
select @start = (select min(creationdate) from votes where votetypeid = 6);
select @maxdates = datediff(d, @start, 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 = 2 -- close
and creationdate > '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] 
from days d
inner join #closevotes r 
        on dateadd(d,rownum, @start) between r.creationdate and r.deletiondate
group by rownum
order by rownum

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results