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

Q&A about the site for system and network administrators

select
  Posts.Id, 
  Posts.id as [Post Link],
  ReviewTasks.Id as ReviewLinkId,
  Posts.CreationDate as Date,
  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,
  sum(CASE WHEN 
    ReviewTaskResults.ReviewTaskResultTypeId <> 7 AND
    ReviewTaskResults.ReviewTaskResultTypeId <> 20 AND
    ReviewTaskResults.ReviewTaskResultTypeId <> 24 AND
    ReviewTaskResults.ReviewTaskResultTypeId <> 21 
  THEN 1 ELSE 0 END) as Other
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) = '##Year:int?2023##'
group by Posts.CreationDate, Posts.id, Posts.Score, Posts.ViewCount, ReviewTasks.Id
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

fork query

edited dec 22

created dec 9 PeterJames