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
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
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
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
select id, reputation, location into #users from users where location is not null and reputation > 1 ;with countries as ( select 1 id, 'Afghanistan' name union select 2, 'Albania' union select 3, 'Algeria' union ...
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##
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##%');