As you may or may not be aware, Stack Overflow's close vote review queue is growing out of control. I felt like graphing the progress of the queue historically, and attempted to persuade the Stack Exchange Data Explorer to give me the data. I was, however, stymied by the lack of a Reviews table. The current SEDE query is a 32-line monster with three CTEs and six INNER JOINs - which still can't account for expired close votes or deleted questions due to limitations of SEDE.
Seeing as expired close votes will not be added, and metadata on deleted posts has been heavily protested, I would like to propose that some data on reviews (of all types, because why not) be added to SEDE. I'm not asking for much - something like the following:
Id INT NOT NULL,
PostId INT,
ReviewTypeId TINYINT NOT NULL,
CreationDate DATETIME NOT NULL,
CompletionDate DATETIME,
PossibleDuplicateId INT,
SuggestedEditId INT,
IsAudit BIT
(As suggested by @Rachel on the deleted-questions-metadata question, maybe null out PostId for deleted posts.)
Even this minimal data (it doesn't include reviewer stats or individual review timestamps) would allow my use case:
WITH CreatedCVReviews(CreationDate, CreationCount) AS (
SELECT CAST(CreationDate AS DATE), COUNT(*)
FROM Reviews
WHERE ReviewTypeId = 5 -- or whatever ID
GROUP BY CAST(CreationDate AS DATE)
), CompletedCVReviews(CompletionDate, CompletionCount) AS (
SELECT CAST(CompletionDate AS DATE), COUNT(*)
FROM Reviews
WHERE ReviewTypeId = 5
GROUP BY CAST(CompletionDate AS DATE)
)
SELECT
Cr.CreationDate AS [Date],
Cr.CreationCount - Co.CompletionCount AS [Net Tasks Added]
FROM CreatedCVReviews AS Cr
INNER JOIN CompletedCVReviews AS Co
ON Cr.CreationDate = Co.CompletionDate
ORDER BY Cr.CreationDate
Other related posts
An answer to Stack Exchange Table Structure postulates that tables with review data may be added in future. That was eight months ago.
The question Can I query close vote reviews on data? is a possible duplicate from last month. I feel that the question you're reading now better presents the evidence; posting this as an answer to the possidupe didn't feel right, and editing the possidupe with this amount of new stuff is just plain frowned upon. (If you disagree, vote it is a duplicate... as desired.)
