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
Top-Ranked user for each country across the world
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##
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##%');
