157

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

31 Answers 31

0

Top Users by Number of Bounties Won in Bangladesh

-- Top Users by Number of Bounties Won in CountryName

SELECT Top 150
Posts.OwnerUserId As [User Link], COUNT(*) As BountiesWon, Users.Location
FROM Votes
  INNER JOIN Posts ON Votes.PostId = Posts.Id
  Inner JOIN Users ON Posts.OwnerUserId = Users.Id
WHERE
  VoteTypeId=9 and LOWER(Users.Location) LIKE LOWER('%Bangladesh')
GROUP BY
  Posts.OwnerUserId, Users.Location
ORDER BY
BountiesWon DESC

Who are the users in a country between this reputation?

Select TOP(100) ROW_NUMBER() OVER (ORDER BY Reputation Desc) as PositionNumber,
Id [User Link], DisplayName, Age, Reputation, WebsiteUrl, Location
From Users
Where LOWER(Location) LIKE LOWER('%##Country##%') AND
Reputation BETWEEN ('##LowerLimit##') AND ('##UpperLimit##')
ORDER BY
Reputation DESC;

Who are the users having reputation between this limit?

Select TOP(100) ROW_NUMBER() OVER (ORDER BY Reputation Desc) as PositionNumber,
Id [User Link], DisplayName, Age, Reputation, WebsiteUrl, Location
From Users
Where Reputation BETWEEN ('##LowerLimit##') AND ('##UpperLimit##')
ORDER BY
Reputation DESC;

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