Find triage review tasks with three LOOKS OK

1

Please login or register to vote for this query.

(click on this box to dismiss)

This query finds all the triage review tasks that has three LOOKS OK for a given year. You may notice that, on average, a review task that has three LOOKS OK will also have one COMMUNITY EDIT. However, one might think that COMMUNITY EDIT implies that the question LOOKS OK, so two LOOKS OK and one COMMUNITY EDIT may be enough to complete the triage review. This query works but is still a work in progress

Meta Server Fault

Q&A about the site for system and network administrators

select
  Posts.Id, 
  Posts.id as [Post Link],
  Posts.Score as Score,
  Posts.ViewCount as Views,
  count(ReviewTaskResults.Id) as Reviews, 
  sum(CASE WHEN ReviewTaskResults.ReviewTaskResultTypeId = 7 THEN 1 ELSE 0 END) as LooksGood,
  sum(CASE WHEN ReviewTaskResults.ReviewTaskResultTypeId = 20 THEN 1 ELSE 0 END) as CommunityEdit,
  sum(CASE WHEN ReviewTaskResults.ReviewTaskResultTypeId = 24 THEN 1 ELSE 0 END) as AuthorEdit,
  sum(CASE WHEN ReviewTaskResults.ReviewTaskResultTypeId = 21 THEN 1 ELSE 0 END) as Flagged
from Posts
inner join ReviewTasks on Posts.Id = ReviewTasks.PostId
inner join ReviewTaskResults on ReviewTasks.Id = ReviewTaskResults.ReviewTaskId 
inner join ReviewTaskResultTypes on ReviewTaskResults.ReviewTaskResultTypeId = ReviewTaskResultTypes.Id
where ReviewTasks.ReviewTaskTypeId = 10
and ReviewTaskResults.ReviewTaskResultTypeId > 1
and Year(Posts.CreationDate) = '2020'
group by Posts.CreationDate, Posts.id, Posts.Score, Posts.ViewCount
having 
  sum(CASE WHEN ReviewTaskResults.ReviewTaskResultTypeId = 7 THEN 1 ELSE 0 END) > 2
  and sum(CASE WHEN ReviewTaskResults.ReviewTaskResultTypeId = 20 THEN 1 ELSE 0 END) > 0
order by Posts.Score DESC

Enter Parameters

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