18

This question already has an answer here:

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.)

marked as duplicate by Dukeling, hims056, Aziz Shaikh, michaelb958, Martijn Pieters Nov 28 '13 at 8:29

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 3
    "but that was 8 months ago" I'd give it 6-8 weeks. – Cole Johnson Sep 11 '13 at 22:52
  • @ColeJohnson That's about 2 months, tops. – michaelb958 Sep 11 '13 at 22:59
  • 2
    @michaelb958 not necessarily so – gnat Sep 11 '13 at 23:27
  • 1
    Perhaps if they lowered the rep needed to review those... You can't find a First Post or Late Answer review to be had, because people are sitting at their keyboard hitting the <refresh> key waiting to get one. – Johnny Bones Sep 12 '13 at 0:01
  • 1
    @gnat I know. I was just interpreting it literally, for whatever reason. – michaelb958 Sep 12 '13 at 0:53

Browse other questions tagged .