90th percentile pending review tasks, by queue


Please login or register to vote for this query.

(click on this box to dismiss)

This is really Shog9's query that I ported from our internal SEDE. It's not going to produce the same results, but it looks pretty close.

Internet of Things

Q&A for builders and users of networked sensors and control devices in the contexts of smart homes, industry automation, or environmental sensors

declare @start as DateTime = ##startDate?getdate()-7##;
declare @end as DateTime = ##endDate?getdate()##;

with PendingTasks as
  select t.ReviewTaskTypeId, 
    (select count(*) from ReviewTasks t2 
      where t2.ReviewTaskTypeId=t.ReviewTaskTypeId
      and dateadd(hour, datediff(hour, 0, t2.CreationDate), 0) <= dateadd(hour, datediff(hour, 0, t.CreationDate), 0)
      and dateadd(hour, datediff(hour, 0, isnull(t2.DeletionDate, @end)), 0) > dateadd(hour, datediff(hour, 0, t.CreationDate), 0) ) Pending
  from ReviewTasks t
  where t.CreationDate > @start
  and t.CreationDate < @end
  group by t.ReviewTaskTypeId, dateadd(hour, datediff(hour, 0, t.CreationDate), 0)

TaskMedians as
  select distinct ReviewTaskTypeId, Percentile_Disc(.9) 
      within group (order by Pending) over (partition by ReviewTaskTypeId) as NintiethReviewTasks
  from PendingTasks

select rtt.Name, NintiethReviewTasks "90% of hourly samples less than this many tasks"
from TaskMedians tm
join ReviewTaskTypes rtt on rtt.Id=tm.ReviewTaskTypeId

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results