# Stack Exchange Data Explorer is up. What now, Wonderland?

If you didn't know already, the Stack Exchange Data Explorer (SEDE) now includes Code Review and all public StackExchange beta sites.

This is our most powerful available tool to tap into our site's data and fetch everything we need to understand exactly where we're headed, and what we need to fix.

Let this Q&A be our Red Pill Lab.

This is your last chance. After this, there is no turning back. You take the blue pill – the story ends, you wake up in your bed and believe whatever you want to believe. You take the red pill – you stay in Wonderland, and I show you how deep the rabbit hole goes. Remember, all I'm offering is the truth – nothing more.

-- Morpheus

Post queries here as answers, vote for interesting and useful queries, share your findings!

Let's stay in Wonderland and see how deep the rabbit hole goes!

• Someone might want to run the query which Grace Note described (and thought was important) in this chat. – ChrisW Jan 16 '14 at 13:18
• None of the links listed work for me. It appears they decided to change "code%20review%20stack%20exchange" to simply "codereview" in the urls. – Daniel Jan 23 '14 at 16:54
• I think I've updated all of them now. I only tested most of them if I broke your URL be assured it was already broken, and I'm sorry. – Daniel Jan 23 '14 at 17:01
• @DanielCook Thanks! – Mathieu Guindon Jan 23 '14 at 17:01

Here's my re-creation of Grace Note's query.

The numbers, as of 2014-01-16, are:

Reputation Threshold # Users % of Avid Users Cumulative % of Avid Users
-------------------- ------- --------------- --------------------------
150                  1095    51.7241         51.7241
200                  793     37.4587         89.1828
500                  132     6.2352          95.418
1000                 51      2.4091          97.8271
2000                 30      1.4171          99.2442
4000                 16      0.7558          100


Status Update, 2014-02-16: There's a bit of progress over the past month. It takes a lot of votes to promote a user to the next threshold!

Reputation Threshold # Users % of Avid Users Cumulative % of Avid Users
-------------------- ------- --------------- --------------------------
150                  1182    51.2798         51.2798
200                  857     37.18           88.4599
500                  158     6.8547          95.3145
1000                 58      2.5163          97.8308
2000                 26      1.128           98.9588
4000                 24      1.0412          100


Status Update, 2014-03-23: It's no use! The percentages are barely budging. Although we have nearly doubled the number of ≥4k users since mid-Janurary, we're also gaining a lot of new avid users. The increased upvote activity has undoubtedly been beneficial, but I'm not convinced that this query is a useful metric of the vitality of the community.

Reputation Threshold # Users % of Avid Users Cumulative % of Avid Users
-------------------- ------- --------------- --------------------------
150                  1299    51.50670000000  51.5067000
200                  924     36.63760000000  88.1443000
500                  167     6.62170000000   94.7661000
1000                 75      2.97380000000   97.7399000
2000                 27      1.07060000000   98.8105000
4000                 30      1.18950000000   100.0000000


Status Update, 2014-06-22:

Reputation Threshold # Users % of Avid Users Cumulative % of Avid Users
-------------------- ------- --------------- --------------------------
150                  1545    51.346          51.346
200                  1109    36.8561         88.2021
500                  188     6.2479          94.45
1000                 93      3.0907          97.5407
2000                 36      1.1964          98.7371
4000                 38      1.2629          100


Here's one way to do something about the problem:

These queries finds posts from users with between 150 and 500 reputation points who have been seen recently. Those posts are likely to be at least somewhat valuable, since those users are known to have made positive contributions already. Please help bump up these members to 500!

There is an updated query available that now includes graduated privilege thresholds here

• Compared to webapps.stackexchange.com, we have more users languishing in the <500 tiers. – 200_success Jan 16 '14 at 23:25
• I think the lesson to learn from Occupy CR is that every single vote counts. Voting for the top 1% users will help towards bringing the regulars where they need to be to sustain a graduated CR, and voting for any of the other 99% users will help bring their average rep above 500. Targeting users between 150 and 500 will work too, but because every single vote counts, we should be voting for questions & answers, not users. – Mathieu Guindon Jan 19 '14 at 17:39
• Thanks for the update! Once thing sticks out to me: it looks like we've lost four 2K users, yet we've still gained eight 4K users. Am I interpreting this data correctly? – Jamal Feb 16 '14 at 9:39
• @Jamal That should mean that 8 users got promoted from the [2000,4000) range to the [4000,∞) range, and 4 users got promoted from [1000,2000) to [2000,4000), resulting in a net loss of four 2k users. – 200_success Feb 16 '14 at 9:46
• Oh, okay. I thought I was looking at it incorrectly. Either way, we've made quite some improvements. – Jamal Feb 16 '14 at 9:48

It is very exciting to get badges on Stack Exchange, but, how many badges are you one vote away from?

Badges can be somewhat complicated to understand, but, sometimes you are just one vote away from that Enlightened.... but, were you the first answer? Are there badges you missed?

Is there a vote you can make which will let someone else earn a badge?

All these questions, and more, can be answered with this simple query:

## One Vote Short

It currently looks something like this:

eq Badge         User Link                     User Name                     Post Link                                                              Score Name

--- ------------- ----------------------------- ----------------------------- ------------------------------------------------------------------------------------------------------------------------------ ----- --------

1   Enlightened   200_success                   200_success                   Can someone review my implementation of a KeyValuePair?                9     Answer

2   Enlightened   200_success                   200_success                   Brute Force Algorithm in C                                             9     Answer

3   Enlightened   Adrian Panasiuk               Adrian Panasiuk               Can someone review my code please?                                     9     Answer

4   Enlightened   ANeves                        ANeves                        Replace strings in a file                                              9     Answer

5   Enlightened   Anton Golov                   Anton Golov                   How can I improve this implementation of Hangman?                      9     Answer

6   Enlightened   Betamos                       Betamos                       Please check my code and coding style.                                 9     Answer

7   Enlightened   c_maker                       c_maker                       What do you think about this smelly test?                              9     Answer

8   Enlightened   Charles Bailey                Charles Bailey                C++ avoid duplicate code in similar methods                            9     Answer

9   Enlightened   ChrisWue                      ChrisWue                      vb.net select case string comparison                                   9     Answer

10  Enlightened   ChrisWue                      ChrisWue                      How do I make this duplicate sort algorithm run faster?                9     Answer

11  Enlightened   ChrisWue                      ChrisWue                      Simple specific classes design                                         9     Answer

12  Enlightened   Fanis                         Fanis                         Why is my web scraping script so slow?                                 9     Answer

13  Enlightened   Gareth Rees                   Gareth Rees                   Simpler method to detect int overflow                                  9     Answer

14  Enlightened   Gareth Rees                   Gareth Rees                   Append nones to list if is too short                                   9     Answer

15  Enlightened   greatwolf                     greatwolf                     Is this implementation of Shamos-Hoey Algorithm OK?                    9     Answer

16  Enlightened   Groo                          Groo                          Can anyone help me in optimizing my code in C#?                        9     Answer

17  Enlightened   Jamal                         Jamal                         Printing certain number of words from file                             9     Answer

18  Enlightened   Jeff Mercado                  Jeff Mercado                  Java Implementation of Quick Sort                                      9     Answer

19  Enlightened   Jeff Mercado                  Jeff Mercado                  Avoid duplicate conditional checks in multiple boolean conditions      9     Answer

20  Enlightened   Jesse C. Slicer               Jesse C. Slicer               I there a better way to handle try in C#                               9     Answer

21  Enlightened   Joonas Pulakka                Joonas Pulakka                Getting a Variable Array into the Scope of another Method in Java      9     Answer


## Naruto (accepted answer with zero score)

Accepted answers without upvotes seem to be an anomaly: when would you accept an answer but not upvote it? I haven't yet experienced a situation where this would be reasonable. However, I can imagine it can happen when the OP doesn't have enough reputation to upvote, and the post gets overlooked by other users "with the power" in the community.

The bottom line: most of the posts you find here probably deserve a vote or two. So if you have some spare votes burning your pockets, this could be a good place to put them.

• "I will be upvoted, BELIEVE IT!" – Dan Pantry Jan 8 '16 at 16:01

# Occupy Code R3view

A while back I had favorited this query for StackOverflow. Now that CR is on the SEDE, I wondered how we'd compare:

How Rich Are the 1% (Active Users)

With this week's data...

SO's breakdown is:

• 27.54% to the 1% (1,610 users)
• 72.46% to the 99%

CR's breakdown is:

• 17.33% to the 1% (11 users, 104,607 rep | average 9,509.72)
• 82.67% to the 99% (1,011 users, 499,066 rep | average 493.64)

I think @200_success is right, WebApps.SE is a very much comparable, graduated site.

WebApps' breakdown is:

• 18.79% to the 1% (11 users, 115,301 rep | average 10,481.91)
• 81.21% to the 99% (994 users, 498,403 rep | average 501.41)

I think we can easily aim at getting the 1%'s average rep to 10K, and the 99%'s average rep to 500 points. All it takes is some more voting.

02/16/2015

Looks like we're very comparable to SO now (by this metric at least).

Who      Rep    Users  PctShareOfWealth AvgUserRep
The 1%  483487  21     28.76140597      23023.19
The 99% 1197540 2018   71.23859403      593.43


12/13/2016

Looks like we've gone past SO now. The 1% are getting richer, and the 99% are getting...richer?

Who      Rep    Users  PctShareOfWealth AvgUserRep
The 1%  1051683 35     32.98290456223   30048.09
The 99% 2136887 3379   67.01709543776   632.4

• Added average user rep to the query; top 1% is now 9696.55, bottom 99% is now 496.63. – Mathieu Guindon Jan 21 '14 at 19:31
• Just checked results for this one; 1% is above 10k and 99% is above 500; the 1% owns a little more than 18% of total rep. – Mathieu Guindon Feb 10 '14 at 1:38
• 1%-ers are at 33% of total rep. – Der Kommissar Dec 13 '16 at 6:49

Some discussion about answer ratios, vote ratios, and other small-metric numbers was discussed in the 2nd monitor recently. This motivated the following report. This report cannot be run on the StackOverflow database - it times out but is OK to run on any of the other sites (the data volumes are much lower).

As an out-of-interest discussion about avid users, and avid user retentions... I modified 200_success's avid-user query to exclude users who are dormant. Consider this result:

• Avid Users of all time
Reputation Threshold # Users % of Avid Users Cumulative % of Avid Users
-------------------- ------- --------------- --------------------------
150                  1207    51.38360000000  51.3836000
200                  872     37.12220000000  88.5057000
500                  161     6.85400000000   95.3597000
1000                 59      2.51170000000   97.8714000
2000                 24      1.02170000000   98.8931000
4000                 26      1.10690000000   100.0000000


and compare it to

• avid users who were active (question, answer, or comment) in the past 2 months
Reputation Threshold # Users % of Avid Users Cumulative % of Avid Users
-------------------- ------- --------------- --------------------------
150                  195     36.38060000000  36.3806000
200                  199     37.12690000000  73.5075000
500                  70      13.05970000000  86.5672000
1000                 31      5.78360000000   92.3507000
2000                 17      3.17160000000   95.5224000
4000                 24      4.47760000000   100.0000000


I put together a Tag Answer Times query; looking at all the answers posted under the top 10 tags over the last 60 days, I was interested in knowing the hour of the day answers were posted.

The query adds 1 to the hour when the minutes are > 30.

By removing the date filter, I get this:

Looks like the best time to ask a question is around 3PM UTC.

Generalist is a badge that is awarded if you have a score of 15 for at least 20 of the top 40 tags on the site.

Small print:

• The top-40 tags are ranked by counting the number of not-closed questions that have that tag.
• There needs to be more than 200 questions for all top-40 tags (Code Review Recently crossed this threshold, and, when we did, the first Generals badges were awarded )
• the Score of 15 is calculated by counting the up votes, and subtracting the down votes for answers you have (not questions). So for a score of 15 you need 15 up votes (150 reputation) for that tag, and no down votes.

Analysis:

Because you need 20 tags of score 15, and because questions can have, at most, 5 tags... it is possible that you can get Generalist by answering just 4 questions (if they are tagged just right), and have a reputation of 200 (200 is minimum in case you rep-maxed...). If you have fewer than 4 answers, or less than 200 rep, you will not show up...

Two queries:

1. Note: only people with 20 or more tags will get Generalist badges.

You can click on the column titles to sort the data. The "User-Link" column does not sort very well, so there is the "User Name" column if you want to find your name.

2. This second query is for tracking an individual's ( YOUR ) progress toward Generalist.

For this second query, you will need your Code Review UserID (the link above points to Community). Your UserID is part of the URL when you click on your profile. Copy that number, and enter it in to the box on the query, and run the query.

The way to understand this report is that it outputs all of the top 40 tags. Then, it calculates the tag popularity (number of questions). The tags are ranked by their popularity. Your score in each tag is also calculated. If your score is less than 15, there will be an exclamation mark in the right-most column. If you have no exclamation mark in your top 20 tags (ranked in the ID column), then you are a Generalist!

Here is a slightly different way to approach the lack of voting pointed out by @tomdemuyt:

Questions with only zero-score answers

Zero-vote answers on questions with no accepted answer, and all answers with at most zero score

Please use these queries and do your part to upvote zombie answers!

# Tumbleweed Prevention

Most of us probably know how it feels to have a question that gets very little attention.

Imagine how it would be to get essentially no attention. No votes. No comments. Very few views.

All you get is a Tumbleweed badge. Yay.

Help prevent the "Tumbleweed" badge by giving the right questions some attention! A question with a single upvote is better than a question with no votes.

(See also a more strict fork of the same base query, used by @RoboSanta.)

It was asked: Anyone know how to formulate the voting data as a pivot:

Here is a first stab at the critical voting factors for CodeReview since 1 Jan 2013.

This does a pivot-table on the voting data, and plots the results as a graph over time.

The answers-per-question ratio was not brought up as being a problem in the Grace Note Review, but it is a persistent 'concern' on Area-51. Currently CodeReview is at 1.8 answers/quesion, which is 'OK'. Area51 claims that 2.5 is a good ratio... So, how to improve?

Well, the answer to that is to answer questions multiple times, but, you also want to be rewarded for that, so which questions would be good candidates for a second review? Well, that would be the:

• Good Questions
• Only one answer though....
• and it's not a 'great' answer (low-votes)
• and it is not an accepted answer
• and the question-owner is still active on CR so she/he can vote/accept your new answer

Questions like the above would be good candidates for a second answer attempt.

And, now you can:

# Categorized lists of tags

I took the top 500 tags and (with the help of a few fellow contributors) made some loosely categorized CREATE TABLE statements that you can drop into your SEDE queries and JOIN to the Tags table.

### Example usage:

CREATE TABLE #DatabaseTags (Name VARCHAR(100));
GO
INSERT INTO #DatabaseTags
VALUES
[...]
GO

-- Top scored database posts
SELECT
[Post Link] = Posts.Id
, [Score] = Posts.Score
FROM Posts
INNER JOIN PostTags ON PostTags.PostId = PostsTags.TagId
INNER JOIN Tags ON PostTags.TagId = Tags.Id
INNER JOIN #DatabaseTags ON #DatabaseTags.Name = Tags.Name
ORDER BY Posts.Score DESC;


Of course, feel welcome to inspire your own lists from this, and point out any errors or improvements that could be made in chat :)

## Ripe zombies

Open questions with answers, at least one answer having score 0, no answer having score > 0 (a.k.a. zombies ready for killing). If you have some spare votes burning your pockets, this could be a good place to put them.

• Every unanswered question is a zombie.
• Every unvoted good answer is a target.

If one answers a question, it is encouraging to get at least one vote (or a comment about how to improve your answer); if you post and get no feedback at all, that's discouraging.

So:

1. The following is a useful query (highly upvoted, on the front page of the SEDE 'featured' queries):

Find interesting unanswered questions

Looks at unanswered questions in your top 20 tags and sorts them by a combined weight which takes into account: score, askers reputation and how well you do on that particular tag

2. I wrote a second query, copying the same criterion for "interesting" as the one above, adding the tag names, and sorting by reverse date:

Interesting zero scored answers

If you run this you'll find answers on topics which you know, which haven't received any votes (or, which hadn't received any at the time when the SEDE data was last collected).

For both queries, type in your numeric User ID, if SEDE can't resolve your ##UserId## automatically.

Good hunting!

We've discussed Unsung Hero for Best of 2013. Here is some data to go with that:

Unsung Heroes

• You might want to come up with some other ORDER BY clause, if the top four users of all time show up as entries 10, 11, 12, and 13 of your Unsung Heroes query. – 200_success Jan 17 '14 at 1:35
• The top four users are sung heroes ;) – konijn Jan 17 '14 at 1:45

# Vote Analysis

Compare year-to-year monthly votes, by type:

• AcceptedByOriginator
• UpMod
• DownMod
• BountyClose
• Deletion
• Undeletion
• ModeratorReview
• ApproveEditSuggestion

• UpMod
• DownMod
• Favorite
• Close
• Reopen
• BountyStart
• BountyClose
• Deletion
• Undeletion
• ModeratorReview
• ApproveEditSuggestion

• ApproveEditSuggestion

• ApproveEditSuggestion

VoteType Analysis Over Time, By Month/Year

Here's a table that illustrates the kind of analysis I see with this dataset:

           2011   2012          2013          2014
January    1043   1705  +63.5%  1190  -30.2%  1689  +41.9%
February   1015   1287  +26.8%  1657  +28.7%
March       971   1483  +52.7%  1435   -3.2%
April       718   1208  +68.2%  1155   -4.4%
May         819    987  +19.8%  1280  +29.7%
June        489   1304 +166.7%  1199   -8.1%
July        659   1553 +135.7%  1284  -17.3%
August      846   1417  +67.5%  1436   +1.3%
September   667   1639 +145.7%  1557   -5.0%
October     980   1475  +50.5%  1812  +22.8%
November    996   1485  +49.1%  2823  +90.1%
December   1420   1031  -27.4%  3712 +260.0%
==========================================================
10623  16574  +56.0% 20540  +23.9%


# Monthly Votes Projection

• Nothing like Excel for pivoting :) – Mathieu Guindon Jan 17 '14 at 15:45
• Strange, it seemed to be going strong throughout 2012 until December, and it never really recovered until the big push started on Meta in October. – David Harkness Jan 19 '14 at 23:31
• @David that would have to be investigated, but I suspect a high abandonment rate in the last quarter of 2012, of the then-regulars; that would explain the non-recovery until a new base of regulars formed. – Mathieu Guindon Jan 20 '14 at 14:44
• @David looking at your profile... do you recall of anything special in 2012.Q4? – Mathieu Guindon Jan 20 '14 at 23:22
• @lol.upvote I've only visited the site on 16 days--10 in the past month. I could have sworn I visited a lot more before that. However, at that time I was knee deep in a rewrite of a major backend app at work. I'd be hard-pressed to recall anything else from that time. :p – David Harkness Jan 21 '14 at 2:46
• @DavidHarkness 16 days, 74 answers? Did you even sleep at all?! – Mathieu Guindon Jan 21 '14 at 2:53
• @lol.upvote Oh, that's specific to meta. It shows my rep from the main site so I didn't notice. 293 days sounds more like it! :) – David Harkness Jan 21 '14 at 3:10
• @200_success took a stab at the pivot table – rolfl Jan 21 '14 at 5:22

### Time to Answer

This query breaks down the average standard deviation of time to answer by month (questions' date asked), for the top 5 tags:

Average time to answer per tag

Stdev time to answer per tag

Stdev @ StackOverflow

Looks like the metric should be revolving at around 50 hours, per Stack Overflow.

Answers that elicited a "wow" comment from the original poster

(I've sorted the results by ascending answer score, to help identify which good candidates to upvote.)

### FizzBuzz on FizzBuzz

This one is just for fun! It returns all questions, as well as performing FizzBuzz on several values related to the questions and users!

• Aaww... missing row_id over creation date for the ultimate meta-fizzbuzz! – Mathieu Guindon Jun 29 '15 at 18:12
• Feel welcome to add that in and update, I don't know how to do that! – Phrancis Jun 29 '15 at 20:24

### Bad Naruto (*)

Selfie accepted answers with zero or negative score, when there are other answers with positive score. Such answers seem suspicious, and might even need moderator attention.

Warning: these are potential bad Narutos. Legitimate false positives may exist.

(*) Naruto (a hat in Winter Bash 2014): accepted answer with zero score

Note that you need to use quote marks in the tag field since it is VARCHAR. Like 'java', etc. Otherwise you'll get an error like "Invalid column: java".