Could we have some example queries, generated by the Stack Exchange team themselves, to use as a reference for the Data Explorer?

Edit: Based on the comments, I need to clarify that I am suggesting creation of a very small number of queries -- maybe 5 -- as a reference for users. I am not suggesting that the SE team write hundreds or thousands of queries or that it needs to moderate user's creations.

There is a huge amount of redundancy in the queries that users have created; there are dozens of attempts to report on progress toward certain badges, questions with 4 votes to close, top answer writers, rapid climbers, and so on. The problem is that many of them do not work.

For example, I have tried out all of the queries I could find that attempt to identify questions with 4 votes to close. Most of them at least look sane -- the SQL logic is reasonable -- but not one of them produces meaningful results. In many cases, the results include question ID numbers that are not hyperlinked, even when it seems like the query should have produced text in that column. Even when the questions are hyperlinked, many of the questions have no votes to close or were closed ages ago (as in several years back). I even tried writing my own query from scratch, but ran into the same problems. I've got 14+ years experience with SQL, so this was more than a little frustrating.

Why is this important? Data Explorer has the potential to be a huge help in the community's ability to help moderate effectively, detect abusive users, and provide helpful feedback on potential bugs to the Stack Exchange team. Right now, though, learning to use Data Explorer is one part bootstrapping, one part guess work, and two parts digging through probably-busted queries written by others. A handful of canonical queries that are known to work and documented with a few inline comments would be tremendously helpful and make Data Explorer far more useful to the community.

Note: I am aware that the API also exists and has a lot of powerful features. My question, however, focuses on what can be done to make Data Explorer better. Being able to write a simple query is, at least in my experience, much faster and easier than working with an API, even a great API... but only if the query works!

  • 1
    If you're unhappy with SEDE's results that are updated about once a week, you can index through every post in SE with the SE API in your favorite code language.
    – Cilan
    Feb 22, 2014 at 17:05
  • 5
    In the specific case of close/reopen votes, there's currently an outstanding issue with the source data, so that needs to be addressed first and foremost.
    – Tim Stone
    Feb 22, 2014 at 17:14
  • @TimStone Good to know - that issue was particularly confusing to me.
    – elixenide
    Feb 22, 2014 at 17:15
  • 2
    Someone else had suggested something like this in passing before, and I agree that it's not an unreasonable idea to have a good set of example queries. What needs to be done in addition to that is to bring in better tools for surfacing queries that work well (and possibly hiding those that don't), and making those changes is on my todo list after I take care of a few other things.
    – Tim Stone
    Feb 22, 2014 at 17:16
  • 1
    @Tim, I think that would likely have been me. Or at least I asked the same in regards to the API. Now that the data explorer works for beta sites, the API is less important than data.se for some simple examples. At any rate Ed, if you want some simple queries, you can check out these ones
    – jmac
    Feb 25, 2014 at 5:11
  • @jmac Did not know about that post; it's a great reference. Thanks.
    – elixenide
    Feb 25, 2014 at 5:13
  • Glad I could help. They're just some basic queries, but should give you an idea of where to start.
    – jmac
    Feb 25, 2014 at 5:48
  • Followup: meta.stackexchange.com/q/239639/162102 Sep 19, 2014 at 21:10
  • 1
    I'm considering this completed because of the examples included as part of the new tutorial. Although they weren't written by the team, they seem to meet the spirit of your request otherwise.
    – Pops
    Jun 27, 2016 at 13:51
  • @Pops Works for me. Thanks!
    – elixenide
    Jun 27, 2016 at 14:08
  • Some of the tutorial examples were derived from suggestions from jmac here, so there was some team involvement. Jun 27, 2016 at 14:29


You must log in to answer this question.