0

Please login or register to vote for this query.

(click on this box to dismiss)

This counts only the +2 bonus for accepting

Q&A for programmers

-- Users who have gained the most rep through accepting answers
-- IN DEVELOPMENT; This counts only the +2 bonus for accepting

SELECT quest.OwnerUserId AS [User Link],         -- Question owner
       COUNT(*)*2 AS "Rep from +2s"              -- Number of accepts x2 rep per accept
FROM Posts quest
INNER JOIN Posts ans ON ans.ParentId = quest.Id
WHERE quest.OwnerUserId != ans.OwnerUserId AND   -- Not self-accept
      quest.AcceptedAnswerId = ans.Id AND        -- Answer is question's accepted answer
      quest.OwnerUserId IS NOT NULL AND          -- Asker is not deleted user
      quest.CommunityOwnedDate IS NULL           -- Qusetion is not CW
      --                                         -- SEDE auto-excludes deleted posts
GROUP BY quest.OwnerUserId
HAVING COUNT(*) > 500
ORDER BY COUNT(*) DESC

---------------------------------------------------

-- SELF TEST 3:

--SELECT Posts.OwnerUserId AS [User Link],
--       COUNT(*)*2 AS "Rep from +2s"
--FROM Posts
--WHERE AcceptedAnswerId IS NOT NULL AND     -- Has accepted answer
--      OwnerUserId IS NOT NULL AND          -- Asker is not deleted user
--      CommunityOwnedDate IS NULL           -- Not CW
--GROUP BY Posts.OwnerUserId
--HAVING COUNT(*) > 400
--ORDER BY COUNT(*) DESC

---------------------------------------------------

-- SELF TEST 2:

--SELECT Id, AcceptedAnswerId, PostTypeId, CommunityOwnedDate
--FROM Posts
----INNER JOIN
--WHERE OwnerUserId = 122607 AND
--      AcceptedAnswerId IS NOT NULL AND
--      PostTypeId IS NOT NULL AND
--      CommunityOwnedDate IS NULL

---------------------------------------------------

-- SELF TEST 1:

--SELECT Id, AcceptedAnswerId
--FROM Posts
----INNER JOIN
--WHERE OwnerUserId = 122607 and AcceptedAnswerId IS NOT NULL

----------------------------------------------------

-- ORIGINAL:

--select p.OwnerUserId as [User Link], COUNT(*)  --
--from Votes p2v                                 --
--inner join Posts p on p2v.PostId = p.Id        --
--where                                          --
--p.PostTypeId = 1 and                           -- questions
--p2v.VoteTypeId = 2 and                         -- upvoted
--p.CommunityOwnedDate is null and               -- not CW
--p.OwnerUserId is not null                      -- ignore deleted users?
--group by p.OwnerUserId                         --
--having COUNT(*) > 400                          --
--order by count(*) desc                         --

fork query

created mar 10 11 Lord Torgamus