I have an idea for encouraging Stack Overflow users to more actively review close votes, based on a theory that I have. Before I do this I want to do a little research to see if I can find any evidence to support my premise.

I am looking for the following statistics; but I am looking to group them by day and observe their values over time (by "privileged user" I mean users who can review votes):

  • Percentage of privileged users who did not review any close votes.
  • Percentage of privileged users who hit their close vote cap.
  • Average number of close reviews conducted per privileged user.
  • Average number of close reviews conducted per privileged user excluding users who did not conduct any reviews.
  • The number of pending close review tasks.

I'd like to go as far in the past as possible, but ideally at least 2-3 years (it's a large time span; I don't know how far back history is kept).

Can somebody help me construct data.sx queries to pull this information? If I could query information about every vote I could analyze it myself (and I'd really like to do this) but query results seem to be limited to 50,000 rows, and so SELECT * FROM Votes WHERE VoteTypeId = 6 doesn't get all the information.

  • What do you mean by "The size of the close review queue"? Also, what time period are you looking for here?
    – Shog9
    Commented Feb 25, 2014 at 4:14
  • @Shog9 Good question. Specifically, I mean the number that is publicly displayed to users in the left-hand column of stackoverflow.com/review - regardless of whether or not that number is accurate. I am looking for as far in the past as possible, but hopefully at least 3 years (I know it's a large time span).
    – Jason C
    Commented Feb 25, 2014 at 4:15
  • 1
    Pending review tasks then.
    – Shog9
    Commented Feb 25, 2014 at 4:16
  • 1
    BTW: if you ever need to work offline with a large amount of data, you're really better off grabbing the dump.
    – Shog9
    Commented Feb 25, 2014 at 4:32
  • @Shog9 I had no idea this existed; I can get everything I need from here. Thanks! How far back does the data go?
    – Jason C
    Commented Feb 25, 2014 at 4:36
  • 1
    It's everything currently visible on the site, @Jason - unfortunately, I don't think you're going to be able to get much from /review here, since most of that data isn't included. Close votes date back to somewhere in July of 2011 though.
    – Shog9
    Commented Feb 25, 2014 at 4:51
  • 1
    % of privileged users who did not review any close votes: large % of privileged users who hit their close vote cap: small Avg number of close reviews per privileged user: few Avg number of close reviews per privileged user ex. users: still few The number of pending close review tasks:* heaps (115K+ and trending up)
    – slugster
    Commented Feb 25, 2014 at 4:53
  • 1
    To be honest, you are probably better to just state your theory and let people pick it to pieces or endorse it - if someone is sufficiently motivated to pull some figures out to prove a point they will.
    – slugster
    Commented Feb 25, 2014 at 4:58
  • @slugster I'm OK with picking it to pieces, but I'm not even going to bother posting it if it doesn't have a fighting chance. I don't mind saying stupid things, I just want to at least try to not be an idiot. (Plus the more solid ground I have, the less Servy will clutter the comments with distracting, disagreeable cruft.)
    – Jason C
    Commented Feb 25, 2014 at 5:00
  • 1
    It's a subject that has been well beaten to death in the past. Your idea might be revolutionary or it may not - have a good think about how to phrase it then float it out there. While it's nice to have people consistently trimming the queue it's not necessarily a problem that "needs" to be "solved".
    – slugster
    Commented Feb 25, 2014 at 5:04
  • @slugster I know. It's a tricky topic to get any serious consideration about these days. It's the fundamental reason why the survey question I just posted here was closed. It's also why I'm trying to phrase my information gathering questions as carefully and unbiased as possible.
    – Jason C
    Commented Feb 25, 2014 at 5:06

2 Answers 2


There's a lot of this that's not possible to obtain from the public data, I'm afraid... Starting with folks who had a given privilege on a given day. Given enough time, you could approximate this using the voting data, but now you're talking about running a reputation recalculation for every person on the site for every day for the past three years. I'm guessing that's probably a bit more work than you want to put in.

Since there's no user information associated with close votes, you're pretty much stuck unable to do the rest of these too...

I can try to get some of this information at some point, but not tonight. For now, I'm going to redirect you to a few related questions:

  • 2
    You get the jump on me again Shog. Curse you and your data-aptitude!
    – jmac
    Commented Feb 25, 2014 at 5:08
  • Thanks for all your help on this. I was hoping it would be a few easy queries. Alas... (Anyways, the data dump will have to wait because at the moment I'm trying to get my head around the fact that there's only ~300MB left on my 2TB drive. Whoops.)
    – Jason C
    Commented Feb 25, 2014 at 5:28

You are going to run in to problems very quickly.

  1. You can't get 'percentage' of privileged users
  2. You can't count review tasks completed
  3. You can't count the review queue size


The data dump is a point in time. Without doing super-complex math to basically re-evaluate the entire reputation calculations by hand, there isn't an easy way to tell how much reputation user A had on date X. Without knowing the number of users with that privilege, there is no way to calculate percentages or averages.

Counting Reviews

VoteTypeId = 6 <> Close Vote Review

Close votes can be cast directly on questions, and with the current way data.se is set up, whether a vote is directly on a question or through the queue is not easily accessible. Again, you could potentially create a workaround by recreating the math involved in calculating how many close votes a question has to see if it got put in the queue, but that would require analyzing every question ever to see its close votes history if such a thing is even possible.

To make things worse, that VoteTypeId no longer works after 2013-06-25 due to the close vote changes. So you're not going to be able to see anything over the past 8 months.

Queue Size

Along with no being able to tell which votes were through a review action, you can't calculate the size of the queue at any given time in any easily-accessible way in data.se. So your last data point will be missing as well.

  • Thanks for this, that's a bit of a bummer. The past 8 months are part of the period I'm most interested in. With the close vote changes, is there an equivalent/similar query that can be done to find votes?
    – Jason C
    Commented Feb 25, 2014 at 5:26
  • 2
    There is no query for data.se. If you are ambitious and know how to use the API, you can get details on what questions were closed by checking the right boxes on certain questions. You would have to loop through 100 questions at a time, or run a certain search to get questions that are closed, and then parse the API with a call like this. That will get you how many users closed it/when it was closed at least.
    – jmac
    Commented Feb 25, 2014 at 5:55
  • Excellent, this looks promising; I'll check it out. I haven't used the API yet.
    – Jason C
    Commented Feb 25, 2014 at 5:57
  • good I can see others answers before I commit with my own. So many times I'd duplicate an existing answer ;) +1
    – user221081
    Commented Feb 25, 2014 at 9:43

You must log in to answer this question.

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