Review Queue Size

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.

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]