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

Question

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

Bookmark: http://data.stackexchange.com/codereview/queries

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!

Answer 1

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:

Welcome intermediate users by reviewing their answers or their questions

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

Answer 2

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

Answer 3

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? Perhaps the OP didn't have enough reputation to upvote, or inexperienced in the community? It could be good to take a look here, some of the posts here probably deserve a vote.

Answer 4

Occupy Code Review (CR)

A while back I had favorited this query for Stack Overflow (SO). 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, Web Applications is a very much comparable, graduated site.

Web Applications' 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

Answer 5

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

Answer 6

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 Stack Overflow database - it times out, but it is OK to run on any of the other sites (the data volumes are much lower).

Answer 7

Generalist badge, and your progression

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. This query ranks all users on the site who could potentially qualify for Generalist.

   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!

Answer 8

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!

Answer 9

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.)

Answer 10

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 greater than 30.

By removing the date filter, I get this:

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

Answer 11

Question/Answer ratio

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:

Run this query to get questions that are good Answer Ratio Candidates

Answer 12

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 :)

Answer 13

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.

Answer 14

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 Code Review since 1 Jan 2013.

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

Answer 15

"Call of Duty - We're on a mission" says,

Your ammunition: 40 votes per day, unlimited answers.

• 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!

Answer 16

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

Unsung Heroes

Answer 17

Vote Analysis

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

Answer votes

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

Question votes

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

TagWiki votes

• ApproveEditSuggestion

TagWikiExcerpt votes

• ApproveEditSuggestion

---

VoteType Analysis Over Time, By Month/Year

Answer Upvotes

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 Upvotes (Q+A)

Monthly Votes Projection

Answer 18

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

http://data.stackexchange.com/codereview/query/170387/time-to-answer#graph

Stdev time to answer per tag

Stdev @ StackOverflow

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

Answer 19

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.)

Answer 20

FizzBuzz on FizzBuzz

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

Answer 21

Code-only answers

Code-only answers (also known as code dumps) don't make good code reviews in general, and require attention, to get improved, or possibly deleted.

Answer 22

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

Answer 23

For bragging rights ;-)

Top answerers for each Tag

(Credit to @rolfl)

Answer 24

Look for zombies by tag

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".