Can I query the SEDE for what fraction of close reviews are initiated by flags?

Question

I would like to know what fraction of the recent close-vote review tasks were initiated by a flag cast by a user under 3k reputation (as opposed to tasks assigned to the queue by a close vote by a 3k+ user). Is there enough data in the Data Explorer to get this information? If so, how can this be done?

There isn't any particularly obvious direct path for this, so I thought of getting the first user that cast the close vote and then matching this against the users that reviewed, since those are public (example), but it seems that the ReviewTaskResults table does not include any information on the users that performed the reviews, for whatever reason.

Answer 1

There is a list of pending flags, but sadly that obviously doesn't have historical data.

Besides that table, there is no information in SEDE regarding if a post was flagged and for what reason. Only public information like who were responsible for closing it is shown. (As a note: if I vote to close, the vote can expire when no one adds a vote to it. After that it can be closed, but my name won't be on it)

Answer 2

You can't query that historical data in SEDE because the flags raised against a post are not part of the public schema.

You can get some information if you query pendingflags:

;with basedata as
(
select sum(case when flagtypeid = 13 then 1 else 0 end) as closeflags
     , sum(case when flagtypeid = 14 then 1 else 0 end) as closevotes
     , postid
from pendingflags
where flagtypeid in (13,14)
group by postid
)

select (
  select count(*) 
  from basedata 
  where closeflags = 0
  ) / 
  cast( (select count(*) 
         from basedata  
         where closeflags = 1 and closevotes > 0) 
  as decimal)

Flagtypeid 13 indicates a close flag against a postid where flagtypeid 14 indicates a real close vote. Unfortunately the table pendingflags only hold data while a post is in the review queue. Once completed you're only left with the close vote data in the posthistory table for rows with posthistorytypeid = 10. That doesn't include if the closing involved close flags.

This is the best I can create on the current schema. Consider opening (or change your question in) a feature request to add a pendingflagshistory if you need the history to be available.

Or you might ask Shog9 to re-run his query for you on the internal SEDE instance.