4

I am trying to find those posts that were edited after a Leave Closed result of a Reopen Votes review queue. For that, I had written the following query in the Stack Exchange Data explorer:

SELECT
 P.Id P_Id,
 P.LastEditDate P_LastEditDate,
 RT.Id RT_Id,
 RTT.Name RTT_Name,
 RT.CreationDate RT_CreationDate,
 RTS.Name RTS_Name,
 RTR.Id RTR_Id,
 RTRT.Name RTTR_Name,
 RTR.CreationDate RTR_CreationDate
FROM ReviewTasks RT
INNER JOIN ReviewTaskTypes RTT
 ON RT.ReviewTaskTypeId = RTT.Id
INNER JOIN ReviewTaskStates RTS
 ON RT.ReviewTaskStateId = RTS.Id
INNER JOIN ReviewTaskResults RTR
 ON RT.CompletedByReviewTaskId = RTR.Id
INNER JOIN ReviewTaskResultTypes RTRT
 ON RTR.ReviewTaskResultTypeId = RTRT.Id
INNER JOIN Posts P
 ON RT.PostId = P.Id
WHERE
 (RT.ReviewTaskTypeId = 6 
  AND 
 RTR.ReviewTaskResultTypeId = 13
  AND
 P.LastEditDate > RTR.CreationDate)
ORDER BY P.LastEditDate DESC;  

The query works fine, but I have a problem with the posts edited the same day (but before) than the Review Task Result, because it has time 00:00:00.

For a lot of the posts returned, I can see in their timeline that they were edited the same day but before of the Reopen Votes review result. In fact, the post is added to the Reopen Votes review queue from an edit, and is the most common case that the review is resolved the same day.

I can see, in the timeline, the time of the result, so I think it can be stored in some field of the Data Explorer database.

If possible, how can I select the time of the Review Task Result in the Data Explorer database?

2

Unfortunately, you can't. It seems like review timestamps are deliberately truncated before they enter SEDE, just like votes: Why is vote time missing in the SE data dump and SEDE?. I'll update the Database schema documentation for the public data dump and SEDE to reflect this. Note that the ReviewTaskResults table doesn't include the user who made the review either (something that can be seen easily on the site itself).

Perhaps even more surprising is that the SuggestedEditVotes table does contain review times, and the user IDs of the reviewers. (The reason that this is a separate table is that the suggested edit queues existed before the generic concept of review queues; some of the contents of this table are also stored in the ReviewTaskResults table.)

| improve this answer | |
0

I edited the query, eliminating the condition that the date of creation of the review task result must be less than the date of the last edition of the post.

I also reduced the selected columns, and added the link to the timeline of the post. Finally, I limited the results to 500, resulting in the following query:

SELECT TOP 500
 P.Id Post_Id,
 P.LastEditDate Post_LastEditDate,
 'site://posts/' + CAST(P.Id AS nvarchar) + '/timeline' Timeline_Link
FROM ReviewTasks RT
INNER JOIN ReviewTaskResults RTR
 ON RT.CompletedByReviewTaskId = RTR.Id
INNER JOIN Posts P
 ON RT.PostId = P.Id
WHERE
 (RT.ReviewTaskTypeId = 6 -- Reopen Review queue
  AND 
 RTR.ReviewTaskResultTypeId = 13) -- Leave Closed result
ORDER BY P.LastEditDate DESC;  

Feel free to run, fork, and/or improve. All suggestions are welcome.

| improve this answer | |

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .