158

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##%');

closed as off-topic by animuson Dec 7 '17 at 2:37

  • This question does not appear to be about the software that powers the Stack Exchange network within the scope defined in the help center.
If this question can be reworded to fit the rules in the help center, please edit the question.

  • I'm voting to close this question as off-topic because it is an unorganized mess, and users would have a better time just using the built-in search feature on Data Explorer than trying to sift through a bunch of unsorted answers on a Q&A pair. – animuson Dec 7 '17 at 2:37
  • @animuson Woe that the Data Explorer site does not have voting itself or shared origin filtering. One is often better off starting from scratch than finding an existing query that does what one wants. Than again, maybe I'm not taking advantage of tweaking to get where I want. – Graham Jan 29 at 13:19
  • Can you help me with this: meta.stackoverflow.com/questions/389757/… – Adrita Sharma Sep 25 at 7:20

31 Answers 31

31

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

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.
  • 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
16

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.

14

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           
  • 4
    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
  • 2
    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. – 299792458 Aug 14 '14 at 6:05
14

Site Happiness

Displays happiness (% upvotes / total votes):

enter image description here

The most unhappy site:

enter image description here

And anywhere in between.

  • 4
    This is impressive :) – Jeremy Thompson Jul 9 '17 at 6:10
  • Fun! ...now, can you make it into a pie chart? – ashleedawg Jul 30 '18 at 5:04
12

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.

12

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   
  • 40
    I wonder who got the honour of being upvoted by Eric Lippert! – Daniel Vassallo Jul 18 '10 at 21:29
  • Minor bug to the Reputation link; @endDate must be declared as datetime, otherwise the following error occurs, The data types date and datetime are incompatible in the subtract operator. – Kyle Kanos Aug 18 '15 at 14:54
12

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.

  • 9
    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
10

Daily Site Activity Graphs with Sliding Average (Smoothing):

This query:

-- Displays a sliding average of questions, answers, and up/down votes per day
-- on a site. 
--
-- Parameters:
--
--    Days        Number of days to look back (relative to data dump date).
--    VScale      Divide vote counts by this number (for graph scaling).
--    WindowSize  Number of days (in addition to current) to include in
--                sliding average window (6 is a week, because 6+current = 7).
--                Set to 0 to see actual values instead of averages.
--
-- http://stackexchange.com/users/305991/jason-c

DECLARE @LastDate datetime
SELECT @LastDate = MAX(CreationDate) FROM PostHistory;

-- Hack to generate list of dates so we can insert dates with 0 items
-- into query below so that they're reflected accurately on the graph and
-- in sliding averages. Note we can't use recursion as max limit is 100 
-- on sede.
WITH DatesOfInterest AS (
  SELECT CONVERT(DATE, DATEADD(DAY, 1 - x.N, @LastDate)) D, y.W
  FROM (SELECT TOP ##Days?365## ROW_NUMBER() OVER (ORDER BY Id) N
        FROM PostHistory) x
  CROSS JOIN (SELECT *
        FROM (VALUES ('Questions'), ('Answers'), ('Votes / ' + STR(##VScale##))) z(W)) y
)

-- This query computes the sliding stats.
SELECT
  D [Date],
  W [What],
  AVG(N) OVER (PARTITION BY W ORDER BY D ROWS ##WindowSize?3## PRECEDING) AvgPerDay
FROM (
  -- This query adds 0's back in to daily events.
  SELECT 
    COALESCE(daily.D, doi.D) D,
    COALESCE(daily.W, doi.W) W, 
    COALESCE(daily.N, 0) N
  FROM 
    DatesOfInterest doi LEFT OUTER JOIN (  
      -- This query counts daily events.
      SELECT CONVERT(DATE, CreationDate) D, 'Questions' W, 1.0 * COUNT(*) N FROM Posts 
        WHERE PostTypeId = 1 AND DATEDIFF(DAY, CreationDate, @LastDate) <= ##Days##
        GROUP BY CONVERT(DATE, CreationDate)
      UNION SELECT CONVERT(DATE, CreationDate) D, 'Answers' W, 1.0 * COUNT(*) N FROM Posts 
        WHERE PostTypeId = 2 AND DATEDIFF(DAY, CreationDate, @LastDate) <= ##Days##
        GROUP BY CONVERT(DATE, CreationDate)
      UNION SELECT CONVERT(DATE, CreationDate) D, 'Votes / ' + STR(##VScale##) W, 1.0 * COUNT(*) / ##VScale?7## N FROM Votes 
        WHERE VoteTypeId IN (2, 3) AND DATEDIFF(DAY, CreationDate, @LastDate) <= ##Days##
        GROUP BY CONVERT(DATE, CreationDate)
    ) daily ON doi.D = daily.D AND doi.W = daily.W
  ) stats
ORDER BY
  [Date], 
  -- Custom sort order for the graph based on what I think looks good
  CASE W WHEN 'Answers' THEN 1 WHEN 'Questions' THEN 2 ELSE 0 END

Can be used to generate graphs of average questions, answers, and up/down votes per day on a site, with a sliding windowed average for smoothing. For example, with a 90 day window size on the Spanish site, we can get a nice view of the site's growth over time:

enter image description here

And much smaller window sizes of course give more interesting short term info. Window size of 0 may be set to just display values directly. See comments at top of query.

8

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
7

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

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     
5

Distribution of User Activity Per Hour

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

5

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.

4

Here are some queries that interested me:

  • 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
  • 1
    Could someone fix the first query? – Werner Dec 14 '14 at 7:13
  • Please help me with this: meta.stackoverflow.com/questions/389757/… – Adrita Sharma Sep 25 at 7:21
4

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

3

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

3

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

2

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

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

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.

2

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.

2

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.

2

My Money For Jam, Improvement

This is an improvement of the popular My Money For Jam query that runs approximately 10x faster (2.6 seconds as opposed to 22.4 seconds for Jon Skeet).

Here is the improved query:

DECLARE @LatestDate DATETIME
SELECT @LatestDate = MAX(CreationDate) FROM Posts
DECLARE @IgnoreDays NUMERIC = 15
DECLARE @MinAgeDays NUMERIC = 60

SELECT TOP 100
  PostId [Post Link],
  1.0 * ([Passive Up Reputation] - [Passive Down Reputation]) / [Days Counted] [Passive Rep Per Day],
  [Passive Up Reputation] - [Passive Down Reputation] [Passive Rep],
  [Passive Up Reputation],
  [Passive Down Reputation],
  [Days Counted]
FROM (
  SELECT
    v.PostId,
    SUM(CASE WHEN v.VoteTypeId = 2 THEN
      CASE WHEN p.PostTypeId = 1 THEN 5 ELSE 10 END
      ELSE 0 END) [Passive Up Reputation],
    SUM(CASE WHEN v.VoteTypeId = 3 THEN 2 ELSE 0 END) [Passive Down Reputation],
    DATEDIFF(DAY, p.CreationDate, @LatestDate) - @IgnoreDays [Days Counted]
  FROM
    Votes v
    INNER JOIN Posts p ON p.Id = v.PostId
  WHERE
    v.VoteTypeId IN (2, 3)
    AND p.CommunityOwnedDate IS NULL
    AND DATEDIFF(DAY, p.CreationDate, @LatestDate) > @MinAgeDays
    AND DATEDIFF(DAY, p.CreationDate, v.CreationDate) > @IgnoreDays
    AND p.OwnerUserId = ##UserId##
  GROUP BY
    v.PostId, p.CreationDate
  ) x
ORDER BY
  [Passive Rep Per Day] DESC

The TOP 100 may be removed, it is only provided for compatibility with the existing My Money For Jam query.

It is now fast enough to pull the top 500 list for all users for a whole site, which was my main goal here:

DECLARE @LatestDate DATETIME
SELECT @LatestDate = MAX(CreationDate) FROM Posts
DECLARE @IgnoreDays NUMERIC = 15
DECLARE @MinAgeDays NUMERIC = 60

SELECT TOP 500
  PostId [Post Link],
  OwnerUserId [User Link],
  1.0 * ([Passive Up Reputation] - [Passive Down Reputation]) / [Days Counted] [Passive Rep Per Day],
  [Passive Up Reputation] - [Passive Down Reputation] [Passive Rep],
  [Passive Up Reputation],
  [Passive Down Reputation],
  [Days Counted]
FROM (
  SELECT
    v.PostId,
    p.OwnerUserId,
    SUM(CASE WHEN v.VoteTypeId = 2 THEN
      CASE WHEN p.PostTypeId = 1 THEN 5 ELSE 10 END
      ELSE 0 END) [Passive Up Reputation],
    SUM(CASE WHEN v.VoteTypeId = 3 THEN 2 ELSE 0 END) [Passive Down Reputation],
    DATEDIFF(DAY, p.CreationDate, @LatestDate) - @IgnoreDays [Days Counted]
  FROM
    Votes v
    INNER JOIN Posts p ON p.Id = v.PostId
  WHERE
    v.VoteTypeId IN (2, 3)
    AND p.CommunityOwnedDate IS NULL
    AND DATEDIFF(DAY, p.CreationDate, @LatestDate) > @MinAgeDays
    AND DATEDIFF(DAY, p.CreationDate, v.CreationDate) > @IgnoreDays
  GROUP BY
    v.PostId, p.CreationDate, p.OwnerUserId
  ) x
ORDER BY
  [Passive Rep Per Day] DESC
2

% Users Active on both Main and Meta

Here's the top 20 as of April 2, 2017:

Site                             Active Main Active Meta Active Both Fraction Active
                                                                        On Both 
-------------------------------- ----------- ----------- ----------- --------------- 
StackExchange.Devops             387         122         119         0.307493540051          
StackExchange.Vegetarian         284         87          84          0.295774647887          
StackExchange.Ukrainian          223         62          61          0.273542600896          
StackExchange.Korean             107         25          24          0.22429906542           
StackExchange.Literature         617         133         133         0.215559157212          
StackExchange.Portuguese         170         33          33          0.194117647058          
StackExchange.Languagelearning   150         31          29          0.193333333333          
StackExchange.Judaism            745         135         129         0.173154362416          
StackExchange.Codegolf           7506        1282        1267        0.168798294697          
StackExchange.Latin              288         47          45          0.15625                 
StackOverflow.Es                 3111        467         459         0.147540983606          
StackExchange.Moderators         118         17          17          0.14406779661           
StackExchange.Sitecore           542         79          77          0.142066420664          
StackExchange.Scifi              8367        1196        1158        0.138400860523          
StackExchange.Esperanto          138         19          19          0.13768115942           
StackExchange.Math               40068       5672        5474        0.136617749825          
StackExchange.Hermeneutics       360         51          49          0.136111111111          
StackExchange.Musicfans          302         45          41          0.135761589403          
StackExchange.Chemistry          2479        344         333         0.134328358208          
StackExchange.Hinduism           450         70          59          0.131111111111          

Single Site

This query can be run on a single site, and outputs the number of users active on the main site, the meta site, both, and the fraction of main users also active on meta. Active date range and min rep threshold can be set:

-- To run this query select a *main* site. The corresponding meta site will
-- be automatically queried. Does not work on MSE or StackApps.
--
-- Output is number of users recently active on main, meta, both, and the
-- fraction of users active on main who are also active on both.
--
-- https://meta.stackexchange.com/a/293155

CREATE TABLE #Results ([Active Main] INT, [Active Meta] INT, [Active Both] INT)

DECLARE @Query NVARCHAR(MAX) =
  'INSERT INTO #Results SELECT
    COUNT(main.Id) [Active Main],
    COUNT(meta.Id) [Active Meta],
    COALESCE(SUM(CASE WHEN main.Id IS NOT NULL AND meta.Id IS NOT NULL THEN 1 ELSE 0 END), 0) [Active Both]
  FROM
    (SELECT Id FROM Users WHERE Reputation > ##MinReputation?1##
     AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays?30##) main
    FULL OUTER JOIN 
    (SELECT Id FROM [' + DB_NAME() + '.Meta].dbo.Users WHERE Reputation > ##MinReputation##
     AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays##) meta
    ON main.Id = meta.Id'

EXEC(@Query)

SELECT 
  *,
  CASE WHEN [Active Main] > 0 
  THEN 1.0 * [Active Both] / [Active Main]
  ELSE 0 END [Fraction Active On Both]
FROM #Results


All Sites

This query shows the same info as above, but for every site.:

-- Output is number of users recently active on main, meta, both, and the
-- fraction of users active on main who are also active on both. Excludes MSE
-- and StackApps, which do not have a corresponding meta.
--
-- https://meta.stackexchange.com/a/293155

DECLARE @db SYSNAME
DECLARE @Query NVARCHAR(max)
DECLARE c CURSOR FOR 
  SELECT Name 
  FROM sys.databases 
  WHERE database_id > 5
  AND name NOT LIKE '%.Meta'
  AND name NOT LIKE 'StackApps%'

CREATE TABLE #Results ([Site] NVARCHAR(100), [Active Main] INT, [Active Meta] INT, [Active Both] INT)

OPEN c
FETCH NEXT FROM c INTO @db
WHILE (@@FETCH_STATUS = 0) BEGIN
  SET @Query =
    'INSERT INTO #Results SELECT
      ''' + @db + ''' [Site],
      COUNT(main.Id) [Active Main],
      COUNT(meta.Id) [Active Meta],
      COALESCE(SUM(CASE WHEN main.Id IS NOT NULL AND meta.Id IS NOT NULL THEN 1 ELSE 0 END), 0) [Active Both]
    FROM
      (SELECT Id FROM ' + QUOTENAME(@db) + '.dbo.Users WHERE Reputation > ##MinReputation?1##
       AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays?30##) main
      FULL OUTER JOIN 
      (SELECT Id FROM ' + QUOTENAME(@db + '.Meta') + '.dbo.Users WHERE Reputation > ##MinReputation##
       AND DATEDIFF(DAY, LastAccessDate, GETDATE()) <= ##ActiveDays##) meta
      ON main.Id = meta.Id'
  EXEC(@Query)
  FETCH NEXT FROM c INTO @db
END;
CLOSE c;
DEALLOCATE c;

SELECT 
  *,
  CASE WHEN [Active Main] > 0 
  THEN 1.0 * [Active Both] / [Active Main]
  ELSE 0 END [Fraction Active On Both]
FROM 
  #Results
ORDER BY
  [Site]
2

Your Top Comments, Network Wide


Here is the query. You'll need your network account ID which can be obtained from your profile URL at https://stackexchange.com. Plug it in and your top comments on all sites will be displayed, with clickable links.

Here is a version that shows top comments globally.

Not going to bother including the source, it can be found in the links above.

If anybody is curious, at the time of this writing, this is the highest voted comment on the entire network.

1

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.

1

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
1

Percentage of Approved/Rejected Suggested Edits Per-Site

I was bored, and nothing solves boredom like stats!

This query grabs the Approvals/Rejects of Suggested Edits on a per-site basis (on the overall outcome of the Suggestion, not the per user-vote breakdown) for comparison purposes. Do you sometimes feel like a site seems to reject more suggestions compared to another? Check and find out!

Shoutouts:
Ability to query across all sites unabashedly stolen from this meta

Caveats:

Meta sites are also included in the query, but due to the nature of meta sites the suggested approvals/rejects on these sites are either

  1. Too small in number to seriously count as a 'pattern'
  2. Skew towards 'approved' due to the nature of the users suggesting the edits (ones that care about the site/rules), or
  3. Don't exist at all (especially on smaller sites)
0

Percentage of accepted answers posted by gold tag badge holders (on that question's tag):

with AcceptedAnswers as
(
  select a.ParentId QuestionId, a.Id AnswerId, a.OwnerUserId
  from Posts a
  where PostTypeId=2
  and exists (select 1 from Posts q where q.AcceptedAnswerId=a.Id)
)

select Format(count(*)*1.0/(select count(*) from AcceptedAnswers), 'P') PctGoldBadgeAcceptedAnswers
from AcceptedAnswers aa
where exists (select 1 
  from PostTags pt
  join Tags t on t.Id=pt.TagId
  join Badges b on b.Name=t.TagName and TagBased=1 and Class=1
  where pt.PostId=aa.QuestionId
    and b.UserId=aa.OwnerUserId)

Credits go to Shog9.

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