Review Queue Size

1

Please login or register to vote for this query.

(click on this box to dismiss)

Calculates the size of a given review queue over time. Review tasks on deleted posts are not available, which might skew the results a bit, but this should be a good enough approximation.

Stack Overflow

Q&A for programmers

DECLARE @ReviewType INT = ##ReviewType:int?2##;

WITH TasksIn([Date], [Count]) AS (
  SELECT
    CAST(CreationDate AS DATE),
    COUNT(CreationDate)
  FROM ReviewTasks AS RT
  WHERE ReviewTaskTypeId = @ReviewType
  GROUP BY CAST(CreationDate AS DATE)
), TasksOut([Date], [Count]) AS (
  SELECT
    CAST(RTR.CreationDate AS DATE),
    COUNT(RTR.CreationDate)
  FROM ReviewTasks AS RT
  INNER JOIN ReviewTaskResults AS RTR
    ON RTR.Id = RT.CompletedByReviewTaskId
  WHERE RT.ReviewTaskTypeId = @ReviewType
  GROUP BY CAST(RTR.CreationDate AS DATE)
)

SELECT
  TIn.[Date],
  TIn.[Count] - TOut.[Count] AS [Delta],
  SUM(TIn.[Count] - TOut.[Count]) OVER (ORDER BY TIn.[Date]
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
    AS [Net]
FROM TasksIn AS TIn
INNER JOIN TasksOut AS TOut
  ON TIn.[Date] = TOut.[Date]
ORDER BY TIn.[Date]

Enter Parameters

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