What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 134 Stack Exchange communities.

Let's collect some interesting queries for the Stack Exchange Data Explorer here.

How many upvotes do I have for each tag? (how long before tag badges?)

SELECT TOP 20 
    TagName,
    COUNT(*) AS UpVotes 
FROM Tags
    INNER JOIN PostTags ON PostTags.TagId = Tags.id
    INNER JOIN Posts ON Posts.ParentId = PostTags.PostId
    INNER JOIN Votes ON Votes.PostId = Posts.Id
WHERE Votes.VoteTypeId=2 
    AND Posts.CommunityOwnedDate IS NULL 
    AND Posts.OwnerUserId = @UserId
GROUP BY TagName 
ORDER BY UpVotes DESC

How high is the accepted percentage of my answers? (am I doing it good?)

SELECT 
    (CAST(COUNT(a.Id) AS float) / (SELECT COUNT(*) FROM Posts WHERE OwnerUserId = @UserId AND PostTypeId = 2) * 100) AS AcceptedPercentage
FROM Posts q
    INNER JOIN Posts a ON q.AcceptedAnswerId = a.Id
WHERE a.OwnerUserId = @UserId 
    AND a.PostTypeId = 2

How many votes do my comments have? (how long before Pundit?)

SELECT 
    COUNT(*) AS CommentCount, 
    Score
FROM Comments
WHERE UserId = @UserId
GROUP BY Score
ORDER BY Score DESC

How high would my reputation approximately be when there was no cap or CW?

SELECT 
    SUM(CASE
        WHEN VoteTypeId = 1 THEN 15 -- Accepted answer.
        WHEN VoteTypeId = 2 AND PostTypeId = 1 THEN 5 -- Upvoted question.
        WHEN VoteTypeId = 2 AND PostTypeId = 2 THEN 10 -- Upvoted answer.
        WHEN VoteTypeId = 3 THEN -2 -- Downvote.
        WHEN VoteTypeId = 9 THEN BountyAmount -- Earned Bounty.
    END) AS UncappedReputation
FROM Votes
    INNER JOIN Posts ON Posts.Id = Votes.PostId
WHERE Posts.OwnerUserId = @UserId

Users with more than 10 accounts on same email (sockpuppets? Oh, Oh, James Brown is Dead!)

SELECT 
    u1.EmailHash, 
    COUNT(u1.Id) AS Accounts, 
    (SELECT CAST(u2.Id AS varchar) + ' (' + u2.DisplayName + '), ' FROM Users u2 WHERE u2.EmailHash = u1.EmailHash FOR XML PATH ('')) AS IdsAndNames
FROM Users u1
WHERE u1.EmailHash IS NOT NULL
GROUP BY u1.EmailHash
HAVING COUNT(u1.Id) > 10
ORDER BY Accounts DESC

Downvote ratio of users with > 10 downvotes (the pessimists ;-) )

SELECT TOP 100 
Id as [User Link],
UpVotes,
DownVotes,
(CAST(DownVotes AS float) / (CASE WHEN UpVotes = 0 THEN 1 ELSE CAST(UpVotes AS float) END)) AS DownVoteRatio
FROM Users
WHERE DownVotes > 10
ORDER BY DownVoteRatio DESC

What is my average answer score? (change PostTypeId to 1 to get average question score)

SELECT 
    AVG(CAST(Score AS float)) AS AverageAnswerScore
FROM Posts
    INNER JOIN Users ON Users.Id = OwnerUserId
WHERE OwnerUserId = @UserId 
    AND PostTypeId = 2

Users with highest average answer score (and having > 100 answers)

SELECT TOP 100
    Users.Id,
    DisplayName,
    Count(Posts.Id) AS Answers,
    AVG(CAST(Score AS float)) AS AverageAnswerScore
FROM Posts
    INNER JOIN Users ON Users.Id = OwnerUserId
WHERE PostTypeId = 2
GROUP BY Users.Id, DisplayName
HAVING Count(Posts.Id) > 100
ORDER BY AverageAnswerScore DESC

Users with high self-accept rates (and having > 10 answers) (the extreme self-learners)

SELECT TOP 100 
    Users.Id AS [User Link],
    (CAST(COUNT(a.Id) AS float) / CAST((SELECT COUNT(*) FROM Posts p WHERE p.OwnerUserId = Users.Id AND PostTypeId = 1) AS float) * 100) AS SelfAnswerPercentage
FROM Posts q
    INNER JOIN Posts a ON q.AcceptedAnswerId = a.Id
    INNER JOIN Users ON Users.Id = q.OwnerUserId
WHERE q.OwnerUserId = a.OwnerUserId
GROUP BY Users.Id, DisplayName
HAVING COUNT(a.Id) > 10
ORDER BY SelfAnswerPercentage DESC

Upvote/Downvote ratio per day of week (more risk on downvotes in weekends?)

SELECT 
    DATENAME(WEEKDAY, CreationDate) AS Day, 
    COUNT(*) AS Amount, 
    SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS UpVotes, 
    SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS DownVotes, 
    (CAST(SUM(CASE WHEN VoteTypeId = 2 THEN 1 ELSE 0 END) AS float) / CAST(SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) AS float)) AS UpVoteDownVoteRatio
FROM Votes
GROUP BY DATENAME(WEEKDAY, CreationDate)

How many days was I active? (Returns amount of days when you've posted at least one answer, may be useful for another statistics since registrationdate isn't always representative for "user activity").

SELECT COUNT(DISTINCT CONVERT(char(10), CreationDate, 111)) AS days
FROM Posts
WHERE OwnerUserId = ##UserId##

Most controversial posts on the Site

Search Stack Overflow Favorites by Tag Name

SELECT Posts.id as [Post Link], Posts.Tags as [Tagged With]

FROM Votes, Posts

WHERE
     (Votes.PostId=Posts.Id) AND
     (Votes.VoteTypeId = 5) AND
     (Votes.UserId=##User:int##) AND
     (Posts.Tags LIKE '%<##TagName##>%') AND
     (Posts.Title LIKE '%##BodyText##%') AND
     (Posts.Body LIKE '%##TitleText##%');
share|improve this question
    
@waffles Great! How did you build this app anyway ? –  Sathya May 15 '10 at 3:46
1  
@Essjaaay I posted a bit of background here: meta.stackexchange.com/questions/49424/stack-exchange-sandbox –  waffles May 15 '10 at 4:10
    
After I log in, I get an error. I'm user 8, btw. –  uɐɯsO uɐɥʇɐN May 15 '10 at 6:57
    
I propose we change this so we do not include query bodies in this post, its too hard to contribute here. Latest drop supports query naming and params. See: cloudexchange.cloudapp.net/stackoverflow/q/467 –  waffles May 20 '10 at 2:30
    
@waffles: nice new feature!! The reason I included raw SQL queries is because I've ranted more than once before when for example stackql was down and the link didn't work while I was more interested in the query itself. True, I can write one myself, but I am not a diehard SQL ninja and I've never worked with TSQL before so it may sometimes take some time :) –  BalusC May 20 '10 at 12:48
1  
FYI, I have started a new "featured query" section cloudexchange.cloudapp.net/stackoverflow/queries which should include most of these –  waffles May 25 '10 at 6:19
1  
The highest average score query is more interesting/accurate if you filter out Community Wiki posts (CommunityOwnedDate IS NULL). Otherwise the averages are skewed by fluff answers to fluff questions getting 30k+ views. –  Aarobot May 29 '10 at 16:43
    
@BalusC This question meta.stackexchange.com/questions/52887/… is referring to your query odata.stackexchange.com/stackoverflow/q/2528, I think. –  Mark Hurd Jun 8 '10 at 8:00
    
Oh nice, thanks @Mark! –  BalusC Jun 8 '10 at 11:23
    
I'm not convinced about the uncapped rep one. It reckons my uncapped would be more than 1000 less than it actually is. So I presume it must be missing counting something. –  Martin Smith Jun 8 '10 at 11:55
1  
@Martin: two possible reasons, the actual bounty rep score cannot be retrieved from the DB (is always calculated as 100 points) and the data dump is always dated from end of from previous month (as of now, the data dates from May 31, 2010, 23:59:59, also see the frontpage: cloudexchange.cloudapp.net ). –  BalusC Jun 8 '10 at 13:19
    
@Balus - Yep that would explain it. I've only hit the rep cap once so was expecting it to be pretty identical. –  Martin Smith Jun 8 '10 at 14:27
    
@Balus, bounty can be calculated now, have a look at the Votes table –  waffles Jun 8 '10 at 22:58
    
Can you do a query for ratio of post keystrokes to comment keystrokes? The chatterbox ratio? I tried what I thought was the right way to do it, but I think I did it wrong because it just hung until I got the "something is wrong" message. –  mmyers Jun 18 '10 at 15:37
    
@mmyers Try this. –  Mark Hurd Jun 18 '10 at 19:02

24 Answers 24

that's neat!

Here are my top tags:

enter image description here

2 of my comments got 6 upvotes... completely unexpected.

share|improve this answer
    
Notice that I still have a ways to go before I get my wxWidgets badge! –  uɐɯsO uɐɥʇɐN May 15 '10 at 2:00
    
Commenting is easy... cloudexchange.cloudapp.net/stackoverflow/q/226 –  mmyers May 15 '10 at 5:36
5  
@mmyers: you pundit!!! –  BalusC May 15 '10 at 14:21

Top Voted Answer Rate

Based on the Accepted Answer Rate query, gives the percentage of answers you have where your answer is the highest voted one on the question, with at least one vote. Basically a measure of "community accepted answer" since asker does not always accept an answer, or accepts one that the community may find less helpful.

share|improve this answer

Because votes don't have times at the moment, I looked at votes per time of day of posts, and per weekday:

Stack Overflow Weekday Hours Hour of week
- Accepted Weekday Hours Hour of week

Meta Stack Overflow Weekday Hours Hour of week

Analysis: Stack Overflow: It is best to ask questions in the 23 hour of the (UTC?) day on a Tuesday, but any work day will do. Asking a question in the 7 hour of Sunday and you're 23% (day) or 60% (hour) more likely to get a downvote.

Answers are much more uniform, both by day and by time of day of post.

Meta Stack Overflow: Totally different character (as expected) -- the worst time to ask is the 23 hour, on a Friday (of course) and the best time is the 18 hour on a Sunday. You're 58.5% (day) or 85.4% (hour) more likely to get a downvote at the former compared to the latter.

Answers on Meta Stack Overflow are best posted at the 21 hour of Friday, and most likely to receive downvotes at the 0 hour of Thursday.

EDIT: Seeing as I described the best and worst times as if they were hours of weekdays, I have included that actual query. So, in fact the best hour for Stack Overflow is 23 Thursday for questions and 23 Wednesday for answers and the worst is 7 Sunday for questions and 1 Saturday for answers.

Meta Stack Overflow really has too few downvotes to break them down to hours of the week, but in any case its best is 4 Sunday for questions and 8 Monday for answers, and its worst is 3 Thursday for questions and 12 Sunday for answers.

EDIT: Added Stack Overflow accepted answer analysis. Note that the latest monthly results have changed the analysis above so Stack Overflow, and Meta Stack Overflow, behaviour hasn't settled down completely yet.

share|improve this answer
    
That are interesting stats. –  BalusC Jun 25 '10 at 21:57

Most-repeated comments on Meta Stack Overflow:

Number Text 
------ ---------------------------------------------------------------------------------------------------------------------------
16      Yes ` ` ` ` ` ` 
12      
12      (-1) for the reasons in my answer. 
11      belongs on meta. 
 8      belongs on meta.stackoverflow.com 
 7      LOL ` ` ` ` ` ` 
 5      (-1) for the reasons in my response. 
 5      belongs on meta 
 5      FTFY! ` ` ` ` ` ` 
 5      No ` ` ` ` ` ` ` ` 
 5      Which browser are you using? 
 5      Why the downvote? 
 4      *facepalm* 
 4      [citation needed] 
 4      _` ` ` ` ` ` ` `_ 
 4      ` ` ` ` ` ` ` ` 
 4      http://meta.stackexchange.com/questions/4/list-of-stackexchange-sites 
 4      I agree. Definitely SOFU's fault. 
 4      Link or it didn't happen 
 4      Oh Crap. Temporal Causality loop. 
 4      see also http://meta.stackexchange.com/questions/42544/provide-some-kind-of-on-the-fly-translation-e-g-french-to-english 
 4      See: http://meta.stackexchange.com/questions/10369/what-was-stack-overflow-built-with 
 4      See: http://meta.stackexchange.com/questions/8401/where-can-i-ask-questions-that-arent-programming-questions 
 4      This belongs on meta.stackoverflow.com 
 4      Why? ` ` ` ` ` ` 

(the second one is some unicode emptiness)


Most-repeated comments on Super User

Number Text 
------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
72     Belongs on superuser.com                                                                                                                                                                                                                                                                                                     
39     You're welcome.                                                                                                                                                                                                                                                                                                              
33     I've proposed a Stack Exchange-based Apple site that this question would be perfect for. Just go  [here](http://area51.stackexchange.com/proposals/151/apple?referrer=EmsxHuwirbI%3d) and click "Follow" to help get the site up and running.                                                                                
29     what operating system?                                                                                                                                                                                                                                                                                                       
29     you're more than welcome.                                                                                                                                                                                                                                                                                                    
28     belongs on superuser                                                                                                                                                                                                                                                                                                         
20     Belongs on SuperUser.                                                                                                                                                                                                                                                                                                        
18     **Avoid asking questions that are subjective, argumentative, or require extended discussion. This is not a discussion board, this is a place for questions that can be answered!**                                                                                                                                           
17     What OS are you using?                                                                                                                                                                                                                                                                                                       
14     Thank you very much!                                                                                                                                                                                                                                                                                                         
13     What operating system are you using?                                                                                                                                                                                                                                                                                         
13     You're more than welcome :)                                                                                                                                                                                                                                                                                                  
12     This is a website support issue. Not in the scope of SU.                                                                                                                                                                                                                                                                     
11     awesome, thanks!                                                                                                                                                                                                                                                                                                             
11     Sorry, but shopping type questions are discouraged on SU. http://meta.stackexchange.com/questions/36056/not-the-shopping                                                                                                                                                                                                     
11     Thanks, question answered!                                                                                                                                                                                                                                                                                                   
11     Which version of Windows?                                                                                                                                                                                                                                                                                                    
10     Should be community wiki.                                                                                                                                                                                                                                                                                                    
10     thank you very much                                                                                                                                                                                                                                                                                                          
10     why the downvote?                                                                                                                                                                                                                                                                                                            
10     You're most welcome.                                                                                                                                                                                                                                                                                                         
9      This belongs on SuperUser.com                                                                                                                                                                                                                                                                                                
9      You're most welcome :)                                                                                                                                                                                                                                                                                                       
9      You're most welcome!                                                                                                                                                                                                                                                                                                         
8      Hello, welcome to Super User. Your question has been migrated here, where it is more adapted. To regain ownership over your question, you should create an account here, and associate it with your Stack Overflow account in user options.                                                                                  
8      Outside the scope of SU. Try asking on one of the sites listed here: http://meta.stackexchange.com/questions/4                                                                                                                                                                                                               
8      Should be community wiki                                                                                                                                                                                                                                                                                                     
8      superuser is for computer hardware and software related questions only. Websites are considered off topic. Please read the FAQ (http://superuser.com/faq).                                                                                                                                                                   
8      Thank you for your answers!                                                                                                                                                                                                                                                                                                  
8      This is not computer related. Please check the FAQ for more information.                                                                                                                                                                                                                                                     
8      Which operating system?                                                                                                                                                                                                                                                                                                      
8      You're very welcome!                                                                                                                                                                                                                                                                                                         
8      You're welcome!                                                                                                                                                                                                                                                                                                              
7      I stand corrected.                                                                                                                                                                                                                                                                                                           
7      Should be a wiki.                                                                                                                                                                                                                                                                                                            
7      thanks for the link.                                                                                                                                                                                                                                                                                                         
7      This belongs on superuser.                                                                                                                                                                                                                                                                                                   
7      What version are you using?                                                                                                                                                                                                                                                                                                  
7      What version of windows?                                                                                                                                                                                                                                                                                                     
6      And http://superuser.com/questions/120461/transfer-time-of-a-cylinder                                                                                                                                                                                                                                                        
6      Community Wiki.                                                                                                                                                                                                                                                                                                              
6      Excellent, thanks!                                                                                                                                                                                                                                                                                                           
6      for what operating system?                                                                                                                                                                                                                                                                                                   
6      Glad I could help.                                                                                                                                                                                                                                                                                                           
6      Hello, welcome to Super User. Please review the FAQ (http://superuser.com/faq) to learn more about how this site works. This site is not a discussion board, this is a place for questions to be asked and answered. As such, you should not post a new answer if what you want to say doesn't actually answer the question. 
6      How is this programming related?                                                                                                                                                                                                                                                                                             
6      http://superuser.com                                                                                                                                                                                                                                                                                                         
6      I've raised http://meta.stackexchange.com/questions/45439/what-to-do-with-which-linux-distro-for-my-old-pc-questions-of-super-user/47075#47075 to discuss creating a faq about linux distributions. Please add any comments you have. Thanks.                                                                                
6      Ok great thanks!                                                                                                                                                                                                                                                                                                             
6      on what operating system?

Obviously people on Super User are mainly:

  • Friendly
  • Copy-pasting their "close explanation"
  • Asking for operating system
  • (and also a lot of "belongs on SU", thanks to SO migrations)

Special award for the anonymous person who is silently advertising his Apple proposal.

share|improve this answer
    
Now the game is: "find who says these comments the most". I start with the (-1) ones: devinb! –  Gnoupi Jul 7 '10 at 10:53
5  
On Stack Overflow: odata.stackexchange.com/stackoverflow/q/6358/…. Lol "Is this homework?" –  KennyTM Jul 7 '10 at 11:32
    
Math.SE is a combination of Super User and Stack Overflow :-) –  Mark Hurd Jun 6 '12 at 12:41

Great mystery

This query will let you see the question which has no answer with a positive score and at least five upvotes:

http://data.stackexchange.com/stackoverflow/s/414/great-mystery-badge

This is a query I made for my badge proposition:

Additional Badge Ideas

share|improve this answer

Country with the best average reputation points, and who leads the reputations's country:

A ranking where Jon Skeet is only third!

Rank Country              NbCountryUsers TotalReputation RepsByUser User Link             LeaderReputation 
---- -------------------- -------------- --------------- ---------- --------------------- ---------------- 
 1   Germany               5764           7187046        1246       Pekka                 159384           
 2   Switzerland           1232           1442799        1171       marc_s                180050           
 3   United Kingdom       13946          15447312        1107       Jon Skeet             435230           
 4   New Zealand           1101           1217831        1106       Greg Hewgill          206714           
 5   Israel                1184           1285590        1085       Eli Bendersky          48880            
 6   United States        48776          48250718         989       Marc Gravell          306309           
 7   France                3150           3091885         981       Darin Dimitrov        311884           
 8   Bulgaria               447            430298         962       Bozho                 164212           
 9   Australia             4611           4355741         944       Stephen C             114909           
10   Netherlands           3145           2943495         935       Henk Holterman        101525           
share|improve this answer
2  
As it stands there's a pretty sizeable bug, in that ", " is mapped to the USA before "UK" is mapped to the UK, thus categorising (for example!) Marc Gravell as a US user... –  AakashM Jun 6 '12 at 15:22
1  
Where's Curaçao (formerly Netherlands Antilles)? –  BalusC Jun 6 '12 at 15:26
    
Do you have any way of ascertaining whether these guys actually belong to the places they claim to belong, I mean from the IP address etc? Otherwise, these statistics are meaningless. –  New_new_newbie Aug 14 at 6:05

While everyone is off from work, these users were still there to help.

My first useless data.stackexchange query! I am starting to become familiar with the schema (I hope)!

share|improve this answer

Your Edit Reviews

Shows you how many times you approved and the edit got approved, you rejected but the edit got approved, you approved but the edit got rejected, and you rejected and the edit got rejected.

Example result:

+-------+----------+----------+
| Count | You...   | They...  |
|-------+----------+----------+
|   672 | Approved | Approved |
|-------+----------+----------+
|    96 | Rejected | Approved |
|-------+----------+----------+
|     9 | Approved | Rejected |
|-------+----------+----------+
|   253 | Rejected | Rejected |
+-------+----------+----------+

Your Supposedly Incorrect Suggested Edit Reviews

Links you to your reviews that you approved but the edit got rejected.

Reviews for a post

Shows all reviews of any type for the specefied post.

share|improve this answer

Average answer score will can be highly skewed just by one great answer. I think it is better to use median to have a good idea on Users' "average" performance:

My median answer score:

SELECT 
    Count(Posts.Id) AS Answers,
    (SELECT MAX(Score) 
     FROM (SELECT TOP 50 percent Score
           FROM Posts as PU
           WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL and PU.OwnerUserId = ##UserId##
           ORDER BY Score
           ) as t
     ) AS MedianAnswerScore
FROM
    Posts
WHERE 
    PostTypeId = 2 and CommunityOwnedDate IS NULL and OwnerUserId = ##UserId##

Users with highest median answer score:

SELECT 
    TOP 100
    Users.Id,
    DisplayName,
    Count(Posts.Id) AS Answers,
    (SELECT MAX(Score) 
     FROM (SELECT TOP 50 percent Score
           FROM Posts as PU
           WHERE PostTypeId = 2 and CommunityOwnedDate IS NULL and PU.OwnerUserId = Users.Id
           ORDER BY Score
           ) as t
     ) AS MedianAnswerScore
FROM
    Posts
  INNER JOIN
    Users ON Users.Id = OwnerUserId
WHERE 
    PostTypeId = 2 and CommunityOwnedDate IS NULL
GROUP BY
    Users.Id, DisplayName
HAVING
    Count(Posts.Id) > 100
ORDER BY
    MedianAnswerScore DESC
share|improve this answer

Distribution of User Activity Per Hour

This is an update from the popular query by krock, and looks good as a graph.

share|improve this answer

How many users can do X?

                                                     Min        Number of
Privilege                             Site           Reputation Users
------------------------------------- -------------- ---------- ---------
Users in the data dump for this site  ==>                  -1   1,582,958
Ask and answer questions              *All*                 1   1,582,958
Participate in per-site meta          *All*                 5     732,492
Create community-wiki posts           *All*                10     606,857
Remove new user restrictions          *All*                10     606,857
Flag posts                            *All*                15     461,111
Vote up questions and answers         *All*                15     461,111
Talk in chat                          *All*                20     407,868
Comment everywhere                    Stack Overflow       50     237,807
Set a bounty on a question            *All*                75     190,449
Create chat rooms                     *All*               100     163,348
Edit community wiki questions         *All*               100     163,348
Vote down questions and answers       *All*               125     140,377
Reduced advertising                   Most sites          200     105,753
View close votes                      *All*               250      98,544
Retag questions                       *All*               500      62,872
Create gallery chat rooms             *All*              1000      35,762
Established User                      *All*              1000      35,762
Create new tags                       Stack Overflow     1500      24,940
Edit questions and answers            *All*              2000      18,890
Suggest and vote on tag synonyms      *All*              2500      14,990
Cast close and reopen votes           *All*              3000      12,424
Approve or reject tag wiki edits      *All*              5000       7,094
Access to moderator tools             *All*             10000       3,225
Protect questions                     *All*             15000       1,921
Perform trusted functions on the site *All*             20000       1,288


Important notes:

  1. The query nominally has data for all sites included in the SEDE (Stack Exchange, Data Explorer).
  2. The privilege levels were taken from the StackExchange API, which seems to be current and accurate on most things.
  3. There are no tables of privilege levels in the SEDE currently.
  4. But the API data does not always agree with the FAQ... Which does not always agree with the Privileges pages
  5. There is currently no way for a SEDE query to know what site's data it is using. Hence, the privilege requirements are shown for all sites, whether they apply or not.
    The DB_Name() and DB_ID() functions can be used for this. Will roll into the query in the next day or two.
  6. The sample table is hand-filtered for Stack Overflow.
share|improve this answer
    
just featured it –  waffles Jun 21 '10 at 11:46

List bounties won by tag names

I'm only just getting to grips with SQL Server syntax, so this has the number of three separate tags to look for, but listing bounties for several tags interleaved together seems useful as these things go.

share|improve this answer

Search your Stack Overflow Favorites matching some [tag]

When you have hundreds or thousands of Favorites on Stack Overflow, often you only want to find those matching a certain tag. The new search engine eliminated the ability to search using infavortes: tag, so this offers a near-replacement for that functionality as long as you only need to search on a tag name.

share|improve this answer

Most common question titles on Stack Overflow:

Title                           Count 
------------------------------- ----- 
regular expression              33    
regular expression help         28    
help with sql query             21    
mysql query help                19    
jquery selector                 16    
sql query problem               15    
jquery selector question        14    
database design question        14    
regular expression question     13    
help with regular expression    13    
mysql query problem             12    
what's wrong with this code?    12    
android application development 12    

Mostly they seem related to magical string selectors of some kind - regular expressions, SQL queries, and jQuery selectors.

share|improve this answer
7  
What's wrong with this code?, nice, nice.. –  BalusC Feb 11 '11 at 12:06
    
The current data shows mysql is getting worse, while regex may even be improving, but object reference not set to an instance of an object has shot to the top. –  Mark Hurd Mar 23 '13 at 18:07

Main query: Quickest badge earners (OBOE fixed, augmented with "days since 1st" column).


Legendary

These users became {Legendary} on 2009-12-06, presumably the day the badge is first introduced.

User Link        DaysMembership
---------------- --------------
Alex Martelli    226
Reed Copsey      298
Mehrdad Afshari  399
Marc Gravell     434
JaredPar         435
Jon Skeet        437
cletus           444
tvanfosson       447

Excluding the above users, these are the Top 10 quickest:

User Link            DaysMembership 
-------------------- -------------- 
polygenelubricants   192            
KennyTM              208            
Pekka                241            
Pascal MARTIN        262            
BalusC               266            
"Neil Butterworth"   329            
Pascal Thivent       429            
tvanfosson           447            
Mark Byers           457            
paxdiablo            495            
share|improve this answer
2  
Nice one. In my case there should however be 75 days off since my first post was 75 days after registration :P –  BalusC Jun 6 '10 at 19:50
1  
@BalusC: But the Epic/Legendary badges weren't introduced until fairly recently, so the dates are likely off for everyone else. –  mmyers Jun 9 '10 at 18:22
    
The counts may be off by a day. Either that, or something's up with Fanatic: a ton of people earned it after being a member for only 99 days according to the query. –  James McNellis Jun 21 '10 at 3:19
    
Enthusiast reports 29 days instead of 30. –  ChrisF Jun 21 '10 at 11:14
    
@mmyers: they were introduced a few days before I hit the Epic, so it's still on time :) Mortarboard however was "too late". According to my /reputation page I already hit it on 2nd day of my SO activity. –  BalusC Jul 6 '10 at 15:49
    
@tvanfosson appears in both lists... –  davidsleeps Dec 12 '11 at 22:21

One query I found interesting is High Standards - it shows users that upvote relatively rarely in comparison to their reputation - so they either have high standards for upvoting or forgot about the upvote button at some point.

Excerpt with the first 10 users (using MinRep=10000, MinUpvotes=0):

User Link              Ratio % Rep    + Votes - Votes 
---------------------- ------- ------ ------- ------- 
Eric Lippert           246000  49208  1       19      
Will Hartung           4628.13 14815  31      3       
gimel                  4059.46 15025  36      1       
unknown                3294.81 25372  76      29      
Guffa                  2835.35 56141  197     32      
Alex Martelli          2308.57 115891 501     32      
AndreyT                1994.12 30512  152     200     
CMS                    1875.68 83281  443     5       
John Feminella         1870.67 38913  207     8       
shahkalpesh            1821.43 10200  55      12
share|improve this answer

Here's a query to retrieve the post ids of your favourites.

share|improve this answer
    
It's showing the users with the most favorites. –  Matthew Crumley Jul 29 '10 at 5:36
    
Fixed . . . . . –  Gelatin Jul 29 '10 at 14:26

Daily voting frequency of Top 200 highest rep users

UpVotesPerDay

User Link                  Reputation Days UpVotes VotesPerDay             
-------------------------- ---------- ---- ------- ------------------- 
Eric Lippert               49208      447  1       0.00223713646532438 
unknown                    25372      364  76      0.208791208791209   
SQLMenace                  21322      690  214     0.310144927536232   
Red Filter                 20765      586  201     0.343003412969283   
:
KennyTM                    45356      208  2741    13.1778846153846    
Marc Gravell               151137     639  8547    13.3755868544601    
Pekka                      52537      263  4151    15.7832699619772    
Daniel Vassallo            34180      209  3321    15.88995215311      

DownVotesPerDay

User Link                  Reputation Days DownVotes VotesPerDay             
-------------------------- ---------- ---- --------- -------------------     
Mark Rushakoff             34783      375  0         0                   
sharptooth                 34226      525  0         0                   
Adam Davis                 28223      673  0         0                   
coobird                    22628      650  0         0                   
:
Tom Hawtin - tackline      28912      663  1939      2.92458521870287    
brian d foy                35890      652  2194      3.36503067484663    
David Dorward              41841      648  2400      3.7037037037037     
Neil Butterworth           83090      508  3476      6.84251968503937    

Reputation

User Link       Reputation Days   Up Down UpPerDay           DownPerDay     
------------------------------------------------------------------------------
Jon Skeet       190431     641  6213  282  9.69266770670827  0.4399375975039     
Marc Gravell    151137     639  8547  456 13.3755868544601   0.713615023474178   
cletus          116287     649  2062  386  3.17719568567026  0.594761171032357   
Alex Martelli   115891     431   501   32  1.16241299303944  0.074245939675174   
tvanfosson      113772     651  3793  267  5.82642089093702  0.410138248847926   
JaredPar        102228     639  4412   62  6.90453834115806  0.0970266040688576  
Greg Hewgill     96521     689  3227  293  4.68359941944848  0.425253991291727   
paxdiablo        89650     651  1392  677  2.13824884792627  1.03993855606759    
Mehrdad Afshari  89075     603  1676  669  2.77943615257048  1.10945273631841    
S.Lott           87778     652  5403  543  8.28680981595092  0.832822085889571   
share|improve this answer
32  
I wonder who got the honour of being upvoted by Eric Lippert! –  Daniel Vassallo Jul 18 '10 at 21:29

(These are just concept queries only for now; it should probably be a comment, but since this is CW I posed it as an "answer" -- everyone is free to take this and actually run with it. If no one picks it up, I'm sure I'll learn enough SQL to be able to write these queries myself eventually.)

Among all votes cast, ever, how many percent were cast within 5 minutes of the question being posted? Within an hour? Within a day? A week? A month? Six months? Etc.

(It seems that the generally accepted hypothesis is that most votes happens in the very early stage of a question's life, so it's good to see this supported by data)

Which non-CW answers continuously trickle upvotes days, weeks, months after they're written?

I'd love to analyze what I call "legacy" answers look like, what the questions are usually about, etc. (because yes, I'd like to have as many of those of my own as possible).

share|improve this answer
    
I have this for your first query: odata.stackexchange.com/stackoverflow/q/7920/… . No logarithmic scale though, and I probably did something wrong because I have many votes before their posts were created. It can look better when the data dump offers visualization (which I'm considering adding myself). –  Kobi Jul 21 '10 at 4:54
    
I noticed Money for Jam and My Money for Jam queries today. Those seem to comply this request. Very interesting data indeed :) –  BalusC Jul 29 '10 at 23:03
1  
Those were clickable :) Sorry for formatting as code, I should know better: meta.stackexchange.com/questions/48560/… –  BalusC Jul 30 '10 at 12:02

Percentage of questions with accepted answers on top 200 tags:

Tag                    TotalQuestion TotalAccepted Percentage 
---------------------- ------------- ------------- ---------- 
generics               2795          2313          82         
string-manipulation    1606          1316          81         
reflection             2257          1789          79         
tsql                   4299          3420          79         
string                 4068          3211          78         
arrays                 4625          3618          78         
regex                  8796          6862          78         
...
android                11134         5727          51         
pdf                    2114          1058          50         
server                 1704          820           48         
facebook               2150          839           39     
share|improve this answer

Here are some queries that interested me:

share|improve this answer
1  
Nice queries! But .. Hey, I wasn't for 255 days continuously active! I registered one and half month before I actually started to post my first answer ;) –  BalusC May 26 '10 at 18:16
    
@BalusC: yes, I know that, which is why I pushed for an official Last 30 Days top users ladder. I'll figure out how to write the query soon enough, or you can just write it and I'll learn from that. –  polygenelubricants May 26 '10 at 18:22
1  
re: badge earners by quickness, we are missing dates on the badge table, already committed a fix will pick it up with the next data dump in a few days –  waffles May 27 '10 at 21:39
    
@waffles: make me an admin =) –  polygenelubricants May 28 '10 at 9:25
    
Could someone fix the first query? –  Werner Dec 14 at 7:13

Find people on Stack Overflow who also work for your company.

I found a few people who work down the hall from me. Interesting stuff if you work at a larger company.

share|improve this answer

Users with highest accept rate of their answers

The MinAnswers parameter is the minimum number of answers for the users shown. Good values are 50 or 10.

Incidentally, looking at the top users on the list, some of them seem to be gaming the system somehow:

  • User ski: 20 of 21 accepted answers, all of them bounties, many of them useless answers.
  • User Irshad Hussain: 10 of 10 accepted answers, almost all of them on questions asked by user air who has a very similar writing style.
share|improve this answer
    
I suggest you post specific meta support requests about this, these accounts should be penalized –  waffles May 27 '10 at 21:42
    
I flagged them for moderator attention. –  interjay May 27 '10 at 21:56
    
Nice one! I actually had this query in mind (also with the reason to spot sockpuppet accounts like this!), but I didn't think about writing it anymore. –  BalusC May 27 '10 at 23:44

You must log in to answer this question.

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