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.

Q&A about the site for gardeners and landscapers

----------------------------------------------------------------------------------
/*
    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
       - % of their overall rep given in bounties
       
      Note: Excludes the "Community' user as that's applied to abandoned users
        who have offered bounty.
*/
----------------------------------------------------------------------------------

WITH OverallRep AS
(
  SELECT
    p.OwnerUserID,
    SUM(CASE
          WHEN VoteTypeID = 1 THEN 15                      -- Accepted answer
          WHEN VoteTypeID = 2 AND p.PostTypeID = 2 THEN 10 -- Upvote on answer
          WHEN VoteTypeID = 2 AND p.PostTypeID = 1 THEN 5  -- Upvote on question
          WHEN VoteTypeID = 3 THEN -2                      -- Downvote
          ELSE 0
         END
        ) AS [TotalRep]
  FROM Votes v
    INNER JOIN Posts p
      ON p.ID = v.PostID
  WHERE p.CommunityOwnedDate IS NULL -- Posts can't be community wiki
  GROUP BY P.OwnerUserID
)
SELECT
  RANK() OVER(ORDER BY SUM(Votes.BountyAmount) DESC) AS Rank,
  Users.Id AS [User Link],
  Users.Reputation AS [Current Rep],
  OverallRep.TotalRep AS [Total Rep],
  SUM(Votes.BountyAmount) AS [Bounty Amount],
  COUNT(Votes.BountyAmount) AS [Bounties],
  CAST(CAST(SUM(Votes.BountyAmount) AS Decimal) / CAST(Users.Reputation AS Decimal) * 100.00 AS Decimal) AS [~ % Current Rep Donated],
  CAST(CAST(SUM(Votes.BountyAmount) AS Decimal) / CAST(OverallRep.TotalRep AS Decimal) * 100.00 AS Decimal) AS [~ % Overall Rep Donated]
FROM Votes
  INNER JOIN Users
    ON Votes.UserId = Users.Id
  INNER JOIN OverallRep
    On Users.Id = OverallRep.OwnerUserId
WHERE Votes.BountyAmount IS NOT NULL
  AND Users.Id <> -1 -- Exclude the 'Community' user
  AND NOT Users.Reputation < 1000
GROUP BY Users.Id,
  Users.Reputation,
  OverallRep.TotalRep
ORDER BY SUM(Votes.BountyAmount) DESC;

fork query

edited nov 10 17

created mar 24 15 Mooz