Amount of questions closed after 4 leading close votes in the Close Vote review queue - 2018

0

Please login or register to vote for this query.

(click on this box to dismiss)

Forked from https://data.stackexchange.com/stackoverflow/query/987014/amount-of-questions-closed-after-3-leading-close-votes-in-the-close-vote-review-q

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)
AND YEAR(rt.CreationDate) = 2018
GROUP BY rtrt.Name;

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

Enter Parameters

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