I wrote a tutorial on importing the Stack Overflow XML files into SQL Server that includes how to bring it into SQL Server, what the tables and fields mean, and how to query it.

I've also built spwho2.com, a front-end for some of the slicing and dicing I'm doing. I've got reports like:

• Top 100 Tags Last Month
• What question tags have the highest closed percentages?
• What question tags get the most views?
• What question tags get favorited the most often?
• What question tags get the most answers?
• What question tags are used by people with the highest reputations?

I'm working on reports about users, answers, badges and comments next.

Update 2009/6/9 - User pages are completely online, although folks with wacko display names are going to have a hard time finding theirs. You can check out Jon Skeet as an example, or go to the home page at http://spwho2.com and click on the first letter of your display name. I'll build a search-box style lookup later. On the individual user stat pages, sometimes the statistics are out of order, and I haven't vetted the statistics yet, so if you see anything odd let me know.

Update 2009/6/11 - On user pages, I've added percentiles so you can see where you rank in relation to other users overall, or for a specific tag. For example, on Jon Skeet's page, you can see that he scored in the 99.89% percentile for Submitted Answers on the .net tag, so you might wonder who beat him. Click on the link for Submitted Answers for .net, and you can see the top 100 submitters for that tag. If you go back to his page, another interesting statistic is that he averaged 0.31 hours to answer .net questions, which might sound pretty good - but he only scored 37.25% there. Click on the link for Avg Hours to Answer .NET questions and you'll see that users are averaging as low as .05. The tricky part to that is that they may have answered a lot less questions, so their average looks artificially low. I still haven't vetted most of these, just having fun building pages out. There's bugs with some tags that have # signs in them, for example.

Update 2009/6/14 - How much would you pay for a web site that does all this? $19.95? $29.95? But wait - there's more! I've added:

• A user & tag search
• On the tag pages, I'm now showing the top 10 related tags for each tag (the tags that also seem to be attached most often to questions)
• On the tag pages, I'm showing the 50 oldest unanswered questions if you want to swoop in and find easy questions to answer. Keep in mind that it's first come first serve - the pages will only be updated when a new database export comes out, so by the time you visit your favorite tag, some of the questions may have been answered by someone else.

Update 2009/6/26 - I'd always had a lot of links in the site back to Stack Overflow, but I've added even more after reading Jeff's post about attribution. The user names are now linked back to their Stack Overflow profile, plus there's a footer on every page that links both to Stack Overflow and to the SO blog. I already have every question linked straight back to the Stack Overflow question - I don't want to include the body of the questions (or the answers) on SPWho2 itself, because I'm not after the Google traffic. I haven't included the questioner names next to the questions yet, but I'll do that in the next round of updates when the next database dump comes out.

Update 2009/7/13 - I just updated it with the June data dump. I haven't QA'd the data due to an upcoming vacation, but hey, that's what you're for, ha ha ho ho.

Update 2010/02/13 - I set up a public-facing SQL Server with the Server Fault, Stack Overflow, Super User, and Meta Stack Overflow data dumps. You can connect to it with SQL Server Management Studio 2008 or Toad for SQL Server. Connection info is at Querying the Stack Overflow Data Dump.

I used Wordle to visualise common substrings in titles (the bigger the more common and/or longer recurring phrases). I add some more fuzziness to my algorithm because there's awfully many synonyms in there.

These are based on the so-export-2009-06 data-dump..

Boring stuff..

• Uncompressed there is 1.3GB of XML data (206.1MB compressed using .7z)

Total number of..

.. users: 88,558 [1]

.. reputation points: 16,199,960 (average of ~182 rep per user) [2]

.. badges awarded: 234,599 [5]

.. questions: 182,742 [3]

.. answers: 698,923 [3]

.. votes: 2,379,537 [4]

UpMod                 1915441
DownMod                178300
AcceptedByOriginator   109549
Deletion                22107
Undeletion               1660
Close                    1492
BountyStart              1449
BountyClose              1379
Offensive                 365
Reopen                    121
InformModerator           112
Spam                       98

Top Ten..

..badges

Teacher           31416
Student           29526
Supporter         25362
Scholar           24150
Editor            23450
Nice Answer       22978
Autobiographer    12751
Critic            11609
Commentator        9831
Popular Question   8721

Also, each of the following badges was awarded only once: [6]

• asp.net-mvc
• best-practices
• cocoa
• django
• eclipse
• f#
• iphone
• jquery
• language-agnostic
• performance

.. viewed questions [7]

1. What is the best comment in source code you have ever encountered? - 297,365 views
   • What's your favorite "programmer" cartoon? - 140,287 views
   • Programmer Jokes -- what's your best one? - 107,065 views
   • What real life bad habits has programming given you? - 100,058 views
   • Great programming quotes - 54,046 views
   • What is your favorite "programmer" t-shirt? - 52,027 views
   • Hidden Features of C# - 49,927 views
   • The Coolest Server Names - 48,448 views
   • Jon Skeet Facts? - 35,868 views
   • How Does Stackoverflow Work? (The Official FAQ) - 34,614 views

.. voted questions [8]

1. What's your favorite "programmer" cartoon? - 682 votes
   • How Does Stackoverflow Work? (The Official FAQ) - 621 votes
   • Hidden Features of C# - 585 votes
   • Could we please be a bit nicer to the noobs? - 451 votes
   • Programmer Jokes -- what's your best one? - 385 votes
   • What is the best comment in source code you have ever encountered? - 359 votes
   • Using what I've learned from stackoverflow. (HTML Scraper) - 352 votes
   • What is the single most influential book every programmer should read? - 340 votes
   • What do you use to keep notes as a developer? - 299 votes
   • What real life bad habits has programming given you? - 269 votes

.. voted answers [9]

• 84629 - 976 votes
• 84624 - 745 votes
• 164556 - 661 votes
• 184673 - 621 votes
• 234170 - 566 votes
• 84576 - 551 votes
• 234138 - 550 votes
• 186309 - 544 votes
• 234419 - 539 votes
• 164451 - 512 votes

.. most answered question [10]

1. "What is the best comment in source code you have ever encountered?" [184618] - 533 points
   • "What real life bad habits has programming given you?" [164432] - 524 points
   • "Great programming quotes" [58640] - 490 points
   • "The Coolest Server Names" [262657] - 450 points
   • "What do you use to keep notes as a developer?" [78756] - 389 points
   • "What was your first home computer?" [102714] - 373 points
   • "Programmer Jokes -- what's your best one?" [234075] - 362 points
   • "What is the single most effective thing you did to improve your programming skills?" [76364] - 332 points
   • "What's your most controversial programming opinion?" [406760] - 302 points
   • "What's Your Motto As A Developer/Programmer?" [81677] - 302 points

.. most comments on posts [11]

1. "Since SQL Server doesn't have packages, what do programmers do to get around it?" [770300] - 107 comments
   • 164556 - 83 comments
   • "How should disputes be handled on Stack Overflow?" [586611] - 79 comments
   • 184673 - 65 comments
   • "Should I be discouraged by StackOverflow?" [587512] - 64 comments
   • 423833 - 58 comments
   • 383257 - 56 comments
   • 629405 - 53 comments
   • 318765 - 50 comments
   • 706756 - 49 comments

Graph..

.. age vs reputation

.. age vs account age

.. rep vs number of questions

.. rep vs number of answers

.. question-to-answer ratio

Queries

Some of the above data was gathered using SQL queries on this database. For future reference, here are the queries used to generate the data:

 1: select count(id) from users;
 2: select sum(reputation), sum(reputation)/count(id) from users;
 3: select posttypeid,count(id) from posts group by posttypeid;
 4: select votetypeid, count(id) from votes group by votetypeid;
 5: select name, count(name) from badges group by name order by count(name) desc limit 10;
 6: select name from badges group by name having count(name) = 1;
 7: select title, viewcount from posts order by viewcount desc limit 10;
 8: select title, score from posts where posttypeid = 1 order by score desc limit 10;
 9: select id, score from posts where posttypeid = 2 order by score desc limit 10;
10: select id, title, answercount from posts order by answercount desc limit 10;
11: select id, title, commentcount from posts order by commentcount desc limit 10;

There are 72 questions on SO that have an Answer marked correct that is both lower in score than the highest scoring answer AND are scored lower than -1.

Here is the list of bad, marked correct, answers:

• http://stackoverflow.com/questions/168427 -25 36
• http://stackoverflow.com/questions/911536 -15 18
• http://stackoverflow.com/questions/216359 -15 46
• http://stackoverflow.com/questions/387801 -14 20
• http://stackoverflow.com/questions/277991 -12 35
• http://stackoverflow.com/questions/60032 -8 4
• http://stackoverflow.com/questions/435620 -8 2
• http://stackoverflow.com/questions/13154 -6 6
• http://stackoverflow.com/questions/842590 -6 12
• http://stackoverflow.com/questions/821415 -5 12
• http://stackoverflow.com/questions/514492 -5 19
• http://stackoverflow.com/questions/36239 -5 6
• http://stackoverflow.com/questions/23287 -4 11
• http://stackoverflow.com/questions/765797 -4 9
• http://stackoverflow.com/questions/812260 -4 3
• http://stackoverflow.com/questions/807712 -3 6
• http://stackoverflow.com/questions/653423 -3 5
• http://stackoverflow.com/questions/609063 -3 0
• http://stackoverflow.com/questions/105788 -3 3
• http://stackoverflow.com/questions/821653 -3 9
• http://stackoverflow.com/questions/746548 -3 3
• http://stackoverflow.com/questions/900019 -3 1
• http://stackoverflow.com/questions/865572 -3 4
• http://stackoverflow.com/questions/736670 -3 19
• http://stackoverflow.com/questions/106179 -3 6
• http://stackoverflow.com/questions/158574 -3 15
• http://stackoverflow.com/questions/182873 -3 4
• http://stackoverflow.com/questions/210383 -3 3
• http://stackoverflow.com/questions/229868 -3 6
• http://stackoverflow.com/questions/167569 -3 7
• http://stackoverflow.com/questions/418612 -3 13
• http://stackoverflow.com/questions/445642 -3 16
• http://stackoverflow.com/questions/163834 -3 3
• http://stackoverflow.com/questions/289537 -3 11
• http://stackoverflow.com/questions/693388 -2 4
• http://stackoverflow.com/questions/536818 -2 3
• http://stackoverflow.com/questions/261164 -2 2
• http://stackoverflow.com/questions/420371 -2 3
• http://stackoverflow.com/questions/430615 -2 8
• http://stackoverflow.com/questions/441563 -2 4
• http://stackoverflow.com/questions/404676 -2 5
• http://stackoverflow.com/questions/549427 -2 0
• http://stackoverflow.com/questions/238697 -2 1
• http://stackoverflow.com/questions/478694 -2 6
• http://stackoverflow.com/questions/398982 -2 32
• http://stackoverflow.com/questions/419638 -2 6
• http://stackoverflow.com/questions/108692 -2 10
• http://stackoverflow.com/questions/112920 -2 25
• http://stackoverflow.com/questions/637870 -2 5
• http://stackoverflow.com/questions/703767 -2 2
• http://stackoverflow.com/questions/539797 -2 11
• http://stackoverflow.com/questions/106336 -2 8
• http://stackoverflow.com/questions/269594 -2 1
• http://stackoverflow.com/questions/554790 -2 1
• http://stackoverflow.com/questions/868632 -2 2
• http://stackoverflow.com/questions/758092 -2 -1
• http://stackoverflow.com/questions/835092 -2 10
• http://stackoverflow.com/questions/717613 -2 2
• http://stackoverflow.com/questions/893781 -2 1
• http://stackoverflow.com/questions/342052 -2 18
• http://stackoverflow.com/questions/546921 -2 3
• http://stackoverflow.com/questions/642532 -2 3
• http://stackoverflow.com/questions/738327 -2 2
• http://stackoverflow.com/questions/188321 -2 2
• http://stackoverflow.com/questions/537577 -2 61
• http://stackoverflow.com/questions/699686 -2 1
• http://stackoverflow.com/questions/891636 -2 6
• http://stackoverflow.com/questions/73385 -2 5
• http://stackoverflow.com/questions/209795 -2 0
• http://stackoverflow.com/questions/296490 -2 4
• http://stackoverflow.com/questions/115291 -2 1
• http://stackoverflow.com/questions/65994 -2 5

SQL:

select Id, 
    (select max(a.Score) from Answers a where a.ParentId = q.Id ) as MaxScore , 
    (select a.Score from Answers a where a.Id = q.AcceptedAnswerId) as  SelectedScore
into #t
from Questions q
where AcceptedAnswerId is not null 

select '- http://stackoverflow.com/questions/' + cast(Id as varchar) 
, SelectedScore
, MaxScore
from #t where SelectedScore < MaxScore
and SelectedScore < -1
order by selectedscore asc

No matter what Jeff says, Fastest Gun in the West is still a massive problem:

If you are the first to post an answer you have an almost 50% chance of it getting accepted, if you post the 5th answer you only have a 2% chance of getting your answer accepted.

Full results are below:

0   50477	46.305
1   26680	24.475
2   14516	13.316
3   7609	6.980
4   4023	3.691
5   2136	1.959
6   1244	1.141
7   790	0.725
8   521	0.478
9   285	0.261
10  193	0.177
11  125	0.115
12  116	0.106
13  66	0.061
14  43	0.039
15  40	0.037
16  28	0.026
17  25	0.023
18  15	0.014
19  14	0.013
20  8	0.007
21  4	0.004
22  2	0.002
23  5	0.005
24  8	0.007
25  1	0.001

select q.Id , 
    (  select count(*) from Answers a1 
    		where a1.CreationDate < (select a2.CreationDate from Answers a2 where a2.Id = q.AcceptedAnswerId)
        		and a1.ParentId = q.Id
    ) as AcceptedAnswerRank 
into #t
from Questions q
where AcceptedAnswerId is not null


select AcceptedAnswerRank, count(*), cast(((count(*) + 0.0) / (select count(*) + 0.0 from #t)) * 100.0 as Numeric(6,3))   from #t
group by AcceptedAnswerRank
order by AcceptedAnswerRank asc

My blog posts based on the data dumps:

• Stack Overflow: Up and Down Voting Pattern Analysis
• Stack Overflow: Badge Analysis Over Time
• Stack Overflow: Down Votes vs. Up Votes vs. Reputation
• Stack Overflow: Voting Patterns in Detail
• The Personalities of Stack Overflow by the Numbers

A couple of Questions here on MSO:

• What makes a SOpedian’s profile worth visiting?
• Is Jon Skeet slowly switching his attention from .net to Java?

Answers with data dump analysis to other folk's Questions on MSO:

• Are people too miserly with up votes?
• Best time to ask Questions on Stack Overflow

I'm very big on visualizing the data and make lots of graphs. Here is one of my favorites:

Down Votes (x-axis) vs. Up Votes (y-axis) vs. Reputation (bubble size)

Well I've been mucking around with the data.

And came up with (a probably meaningless) stat that calculates the odds that when you post an answer it becomes accepted. Unfortunately there seems to be no way to filter out community wiki stuff (Jeff let me know this will be in the next dump). So that is mixed in.

For me when I post an answer about 21% percent of the time it will get accepted. The person with the highest ratio, is not Jon Skeet :) The prize goes to rq, who 73% of the time posts the accepted answer to a question. (I excluded people with less than 1000 reputation.)

Top 16 are:

rq                  4596    33  45  0.733333333 http://quirkygba.blogspot.com
Daniel LeCheminant  62055   113 189 0.597883598 
NULL                17637   16  27  0.592592593 NULL
Jb Evain            36702   19  34  0.558823529 http://evain.net/blog/
Todd White          30833   16  29  0.551724138 http://code.logos.com
Rafael              80720   24  44  0.545454545 http://kukawski.pl
Eric Rosenberger    41624   30  56  0.535714286 
Paolo Bergantino    16417   350 679 0.515463918 http://www.rootspot.com
Alex Koshelev       19772   17  33  0.515151515 http://webnewage.org/
Ben Gottlieb        6694    108 212 0.509433962 http://www.standalone.com
JeniT               6739    11  22  0.5         http://www.jenitennison.com/
Bittercoder         4843    18  36  0.5         http://blog.bittercoder.com
John Siracusa       164     19  38  0.5         http://arstechnica.com/staff/fatbits/
Ayman               40005   76  152 0.5         http://aymanh.com
Gdeglin             83491   33  66  0.5         http://www.inigral.com
Miles               64474   34  68  0.5

select OwnerUserId, case 
    when exists (select 1 from Posts p2 where p.ParentId = p2.Id and p2.AcceptedAnswerId = p.Id) then 1
        else 0 
    end as ItsRight
into #t
from Posts p 
where PostTypeId = 2


select OwnerUserId, sum(ItsRight) as [Accepted Answers], count(*) as [Total Answers],  
  (cast (sum(ItsRight) as float) / cast(count(*) as float)) as Ratio 
into #UserRatios
from #t
group by  OwnerUserId
having sum(ItsRight) > 0 and count(*) > 20 
order by cast (sum(ItsRight) as float) / cast(count(*) as float) desc

select DisplayName, r.*, WebsiteUrl, Reputation from #UserRatios r
join Users on Id = OwnerUserId
where Reputation > 1000
order by Ratio desc

I wrote a small program to import the data in a PostgreSQL database. My first analysis was on the interval between an article and the votes:

• 54 % of the votes occur the same day as the post ("fastest gun in West" syndrome)
• but you still have 27 % of the votes which occur more than a week after the post

I also studied the interval between a question and an accepted answer.

• 84 % of the accepted answers were posted on the same day as the question (again, the "fastest gun in West" syndrome)
• but 4 % were posted more than one week after the question.
• the longest delay is more than nine months (for "Best method for varchar date validation in Sybase (T-SQL)?")

Graphs and technical details can be found in my original article (in French).

Here are some things I found from analyzing data that Jeff gave me before making the data generally available.

Question statistics

Reputation statistics

Voting patters

I added it all up in my head, and found that Jon Skeet's reputation velocity is a significant fraction of the speed of light.

Which is good, because photons could use the competition!

Very interesting stats. I have imported the XML into a MySQL database and the data dump is available here http://stackoverflow.com/questions/1008141/stackoverflow-data-for-mysql-closed

Happy data mining!