Reopen review outcome statistics (excluding most audits)

1

Please login or register to vote for this query.

(click on this box to dismiss)

See also https://data.stackexchange.com/stackoverflow/query/794031/reopen-review-outcome-statistics and https://data.stackexchange.com/stackoverflow/query/794412/reopen-review-outcome-statistics-excluding-good-audits

Stack Overflow

Q&A for programmers

With ReopenTasksWithLastActivity as (
  Select
    Task.Id as TaskId,
    Max(Event.CreationDate) as LastEventDate,
    Max(CloseEvent.CreationDate) as LastCloseDate,
    Max(Comment.CreationDate) as LastCommentDate
  From
    ReviewTasks as Task
    Left Join PostHistory as Event
      on Event.PostId = Task.PostId
      and Event.CreationDate < DateAdd(Day, 1, Task.CreationDate)
    Left Join PostHistory as CloseEvent
      on CloseEvent.PostId = Task.PostId
      and CloseEvent.PostHistoryTypeId = 10  -- close
      and CloseEvent.CreationDate < DateAdd(Day, 1, Task.CreationDate)
    Left Join Comments as Comment
      on Comment.PostId = Task.PostId
      and Comment.CreationDate < DateAdd(Day, 1, Task.CreationDate)
  Where
    ReviewTaskTypeId = 6  -- reopen
  Group By
    Task.Id
)
Select 
  Cast(DateAdd(Day, -DatePart(Day, Task.CreationDate) + 1, Task.CreationDate) as Date) as [Date],
  Count(*) as [Total],
  Sum(Case When ReviewTaskStateId = 1 Then 1 Else 0 End) as [Pending],
  Sum(Case When ReviewTaskStateId = 3 Then 1 Else 0 End) as [Invalidated],
  Sum(Case When ReviewTaskStateId = 2 and ReviewTaskResultTypeId in (12, 14) Then 1 Else 0 End) as [Reopen],
  Sum(Case When ReviewTaskStateId = 2 and ReviewTaskResultTypeId = 13 Then 1 Else 0 End) as [Leave Closed]
From
  ReopenTasksWithLastActivity
  Join ReviewTasks as Task on Task.Id = TaskId
  Join PostsWithDeleted as Question on Question.Id = Task.PostId
  Left Join ReviewTaskResults as Result on Result.Id = Task.CompletedByReviewTaskId
Where
  ReviewTaskTypeId = 6  -- reopen
  and (
    -- exclude probable audits where the post exists and has never been closed before the review...
    Question.DeletionDate is not null
    or LastCloseDate is not null
  )
  and (
    -- ...or where the post was deleted before the review
    Question.DeletionDate is null
    or Question.DeletionDate >= Task.CreationDate
  )
  and (
    -- ...or where there is no post history event or comment within 7 days before the review
    Question.DeletionDate is not null
    or LastEventDate >= DateAdd(Day, -7, Task.CreationDate)
    or LastCommentDate >= DateAdd(Day, -7, Task.CreationDate)
  )
Group By
  Cast(DateAdd(Day, -DatePart(Day, Task.CreationDate) + 1, Task.CreationDate) as Date)
Order By
  [Date]

Enter Parameters

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