Amount of questions closed after 3 leading close votes in the Close Vote review queue

0

Please login or register to vote for this query.

(click on this box to dismiss)

Amount of posts closed (and not closed) after 'n' leading close votes in the Close Vote review queue (counts/consider the vote/flag which triggered the review activity) AND with at least 5 close/do not close votes in total (this is to minimize the effect of binding votes cast by moderators or by tag gold badge holders). The default setup in query measures 3 leading close votes, but it can be tweaked for counting 2 and 4 leading close votes as well. Just comment and/or uncomment the INNER JOIN clauses in the 'qualified_posts' CTE (see comments' instructions there).

Stack Overflow

Q&A for professional and enthusiast programmers

WITH vote_sequence AS -- Sequence of votes per post in Close review queue.
(
 SELECT p.Id,
        rtr.ReviewTaskResultTypeId,
        ROW_NUMBER() OVER(PARTITION BY p.Id ORDER BY rtr.Id ASC) AS [Sequence] 
 FROM PostsWithDeleted p
 INNER JOIN ReviewTasks rt ON rt.PostId = p.Id
 INNER JOIN ReviewTaskResults rtr ON rtr.ReviewTaskId = rt.Id
 WHERE rt.ReviewTaskTypeId = 2 -- Tasks in 'Close Votes' review queue.
       AND rt.ReviewTaskStateId = 2 -- Review was completed.
       AND rtr.ReviewTaskResultTypeId IN (5, 6, 8) -- Edit, Close, Do Not Close (Leave Open); respectively.
),
minimum_rounds AS -- Minimize votes from users with binding vote privilege (moderators and gold tag badge holders).
(
 SELECT vs.Id
 FROM vote_sequence vs
 GROUP BY vs.Id
 HAVING count(vs.Sequence) >= 4 -- At least 5 'Close' or 'Do Not Close' votes (first close vote comes from outside the queue).
),
-- close_round1 is the 'Close' vote/flag which triggered the review.
close_round2 AS -- First vote in the queue (second in total) is 'Close'.
(
 SELECT vs.Id
 FROM vote_sequence vs
 WHERE vs.ReviewTaskResultTypeId = 6 AND vs.Sequence = 1
 GROUP BY vs.Id
),
close_round3 AS -- Second vote in the queue (third in total) is 'Close'.
(
 SELECT vs.Id
 FROM vote_sequence vs
 WHERE vs.ReviewTaskResultTypeId = 6 AND vs.Sequence = 2
 GROUP BY vs.Id
 ),
 close_round4 AS -- Third vote in the queue (fourth in total) is 'Close'.
(
 SELECT vs.Id
 FROM vote_sequence vs
 WHERE vs.ReviewTaskResultTypeId = 6 AND vs.Sequence = 3
 GROUP BY vs.Id
 ),
qualified_posts AS -- Set criteria for amount of leading close votes.
(
 SELECT DISTINCT mr.Id
 FROM minimum_rounds mr
 INNER JOIN close_round2 cr2 ON cr2.Id = mr.Id -- 2 leading close votes.
 INNER JOIN close_round3 cr3 ON cr3.Id = mr.Id -- line 53 + line 54: 3 leading close votes.
-- INNER JOIN close_round4 cr4 ON cr4.Id = mr.Id -- line 53 + line 54 + line 55: 4 leading close votes.
)
-- Count how many posts among 'qualified posts' ended up: closed and opened.
SELECT rtrt.Name AS [Close Vote Type],
       count(qp.Id) AS [Count of Qualified Posts]
FROM qualified_posts qp
INNER JOIN ReviewTasks rt ON rt.PostId = qp.Id
INNER JOIN ReviewTaskResults rtr ON rtr.Id = rt.CompletedByReviewTaskId
INNER JOIN ReviewTaskResultTypes rtrt ON rtrt.Id = rtr.ReviewTaskResultTypeId
WHERE rtr.ReviewTaskResultTypeId IN (6, 8)
GROUP BY rtrt.Name;

-- Linked posts:
-- - SO: https://meta.stackoverflow.com/questions/380327/close-a-question-when-it-gets-3-leading-close-votes

Enter Parameters

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