12

NEWS FLASH: After 6-8 weeks of waiting, you can now write queries for beta sites using the data explorer

The data explorer uses TSQL which seems to be similar to regular SQL with a few variations. You can browse existing queries and yoink stuff from them as well.

For instance, taking this query from Jon Ericsson, I created a version for The Workplace. I am horrible at SQL though, as I am not a programmer.

But I'm sure some of you are! Let's try to identify things that we as a community want to look at, such as:

  • Are close votes increasing or decreasing?
  • Is our ratio of up/downvotes increasing or decreasing?
  • Are we getting more one-off questions, or are there regular users?
  • Are questions after a popular question of higher or lower quality?

If you create a query, just link it here, and we can all take a look at it and collaborate on it, and we can track the data we get from it.

5
  • 6
    This is great news!
    – jmort253
    Jan 16 '14 at 8:11
  • Is there any way to click on these queries and get current results displayed, for those of us who are just idly wondering and don't want to spend time learning the query interface right now?
    – keshlam
    May 12 '16 at 1:43
  • @keshlam they are all current if you click the links to the data explorer.
    – jmac
    May 12 '16 at 1:44
  • HM. When I clicked one, I saw the query language. May have missed an execute button.
    – keshlam
    May 12 '16 at 1:45
  • @keshlam just click 'run' under the code.
    – jmac
    May 12 '16 at 1:46

10 Answers 10

Active Oldest Votes
7

Top Close Voters for Closed Questions:

  1. gnat - 3752
  2. Jim G. - 2027
  3. IDrinkandIKnowThings - 1864
  4. jcmeloni - 1182
  5. Christopher Estep - 1053
  6. Michael Grubey - 813
  7. Lilienthal - 808
  8. Jan Doggen - 772
  9. Dawny33 - 748
  10. scaaahu - 704

EDIT: @gnat is correct: These stats are skewed because close votes cast on deleted posts are not included in Data.SE.

8
  • 2
    I updated the query to include everyone with close vote privileges. It was set to 3k which is correct for graduated sites, but 500 for beta sites, so the list is significantly longer.
    – jmac
    Jan 16 '14 at 3:46
  • @jmac: Thanks! I forgot about that!
    – Jim G.
    Jan 16 '14 at 4:34
  • 3
    worth noting that these stats are skewed because close votes cast on deleted posts are not included in Data.SE.
    – gnat
    Jan 17 '14 at 16:03
  • 2
    Wow I need to vote to close more!
    – enderland
    Jan 17 '14 at 19:07
  • 2
    @enderland Naahh it just means you've been voting to close all the right things, since they've since been deleted! :) Don't forget there is a script which auto-deletes low scoring closed questions that meet a specific criteria.
    – Rachel
    Jan 20 '14 at 23:40
  • 2
    I edited your query title slightly, as this query only pulls data from the PostHistory table and aggregates the users who closed a question. You're not actually able to query Close/Reopen votes directly since they expire and get removed from Data.SE.
    – Rachel
    Jan 21 '14 at 15:52
  • @Rachel: Thanks!
    – Jim G.
    Jan 21 '14 at 19:19
  • @JimG. I notice you just edited this. If you are planning to update these statistics from time to time (which is not bad), I suggest also including the date the statistics were taken in the post. I don't think just depending on the last edited date is that reliable.
    – David K
    May 12 '16 at 12:46
5

Top Reopen Voters for Reopened Questions:

  1. 92 Chad
  2. 89 gnat
  3. 81 enderland
  4. 79 Rachel
  5. 49 RhysW
  6. 42 jmort253
  7. 42 jcmeloni
  8. 42 CMW
  9. 37 jmac
  10. 33 Joe Strazzere

Nice job guys! Editing + Reopening is part of what is making this site great :)

(It should be noted that you can't query Close/Reopen votes directly, which is why this query is based off the PostHistory table and successfully reopened questions only. There's a meta proposal here to change that.)

1
  • 1
    @JimG. Fixed :) Apparently, they renamed The Workspace on Data.SE, breaking all queries linking to it.
    – Rachel
    Jan 29 '14 at 3:08
4

# Asked, # Closed, and # Re-Opened per week

enter image description here

Keep in mind that this query evalutes Post Activity per week, not new posts and their current status.

For example it shows you how many questions were closed in a week, regardless of when they were posted, instead of how many questions were asked that week but are now closed.

1
  • This is now broken: here is a fixed version.
    – ArtOfCode
    Apr 14 '15 at 18:09
3

Here is a query that shows upvotes, downvotes, questions, and answers per day.

Use these for trends and the like. You can probably relatively easily include stuff like close votes as well if you felt compelled. I am just awful at SQL.

2

Answers with Under 50 Words

Most of these should be flagged and/or deleted depending on score and content, based on the discussion here.

2

Tag Wikis that are Too Short

This could be useful for expanding the tag wikis that are empty or very close to it.

The cut-off of 40 characters for summary and 100 characters for full wiki were plucked out of thin air and adjusted slightly (there were a bunch of decent summaries between 40 and 49 characters), so feel free to tweak it as needed.

Some ways to improve this query that I haven't done yet:

  • Detect which tag wikis are plaintext and need better formatting
  • Detect which tag wikis are word for word copies of the summary
  • Ignore tags that are already synonyms of other tags completed
1

Number of Questions by Answer Count.

This will be used shortly for cleaning up some of those outliers. We have over 700 questions with over 5 answers. There is something wrong with that...

(This is a better query that polls questions that have never been closed, but leaves out those that were re-opened, if someone good at SQL could fiddle with it so it only shows the results for open questions, that'd be swell)

You must log in to answer this question.

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