select YEAR(Revs.CDate) as Year, count(*) as Posts, sum(R...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Meta Server Fault

Q&A about the site for system and network administrators

select 
  YEAR(Revs.CDate) as Year, 
  count(*) as Posts, 
  sum(Revs.LooksGood) as LooksOK, 
  sum(Revs.CommunityEdit) as CommunityEdit, 
  sum(Revs.AuthorEdit) as AuthorEdit,
  sum(Revs.Flagged) as Flagged
  from
(
select
  Posts.Id,
  Posts.CreationDate as CDate,
  Posts.id as [Post Link], 
  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 Posts.CreationDate > '2023-01-01'
-- and Posts.CreationDate < '2024-01-01'
group by Posts.CreationDate, Posts.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
) Revs
group by YEAR(Revs.CDate)
order by YEAR(Revs.CDate)

Enter Parameters

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