Most Bountiful Users

1

Please login or register to vote for this query.

(click on this box to dismiss)

Sums up the bounty amounts users have given. Also ranks them based on their total bounty amounts and gives a percentage of total bounty over their current rep as well as total bounty over their supposed rep if they weren't rep-capped.

Amateur Radio

Q&A for amateur radio enthusiasts

----------------------------------------------------------------------------------
/*
    Displays Bounty givers ranked on their overall bounty amounts. 
      Also includes their:
       - Current Rep, 
       - Total Rep (if they hadn't been rep-capped, see http://data.stackexchange.com/scifi/query/328997/rep-without-rep-cap-fixed-with-correct-answer-score)
       - Bounty amount total
       - Number of bounties
       - % of their current rep given in bounties
       
      Note: Excludes the "Community' user as that's applied to abandoned users
        who have offered bounty.
*/
----------------------------------------------------------------------------------

SELECT
  RANK() OVER(ORDER BY SUM(Votes.BountyAmount) DESC) AS Rank,
  Users.Id AS [User Link],
  Users.Reputation AS [Current Rep],
  (
    SELECT SUM(Case When VoteTypeID = 1 Then 15 WHEN (VoteTypeID=2 AND p.PostTypeID=2) THEN 10 WHEN (VoteTypeID=2 AND p.PostTypeID=1) THEN 5 WHEN VoteTypeID=3 THEN -2 ELSE 0 END)
    FROM Votes v
    INNER JOIN Posts p ON p.ID = v.PostID
    WHERE p.OwnerUserID = Users.Id AND p.CommunityOwnedDate IS NULL
  ) AS [Total Rep],
  SUM(Votes.BountyAmount) AS [Bounty Amount],
  COUNT(Votes.BountyAmount) AS [Bounties],
  CAST(CAST(SUM(Votes.BountyAmount) AS Decimal) / CAST((
    SELECT SUM(Case When VoteTypeID = 1 Then 15 WHEN (VoteTypeID=2 AND p.PostTypeID=2) THEN 10 WHEN (VoteTypeID=2 AND p.PostTypeID=1) THEN 5 WHEN VoteTypeID=3 THEN -2 ELSE 0 END)
    FROM Votes v
    INNER JOIN Posts p ON p.ID = v.PostID
    WHERE p.OwnerUserID = Users.Id AND p.CommunityOwnedDate IS NULL
  ) AS Decimal) * 100.00 AS Decimal) AS [~ % Current Rep Donated],CAST(CAST(SUM(Votes.BountyAmount) AS Decimal) / CAST(Users.Reputation AS Decimal) * 100.00 AS Decimal) AS [~ % Overall Rep Donated]
  
FROM Votes
  INNER JOIN Users
    ON Votes.UserId = Users.Id
WHERE Votes.BountyAmount IS NOT NULL
  AND Users.Id <> -1 -- Exclude the 'Community' user 
GROUP BY Users.Id,
  Users.Reputation
ORDER BY SUM(Votes.BountyAmount) DESC;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results