90th percentile pending review tasks, by queue

0

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.

スタック・オーバーフロー

プログラマーとプログラミングに熱心な人々のための質問と回答

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

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