Most Bountiful Users

1

(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.

``````SELECT
RANK() OVER(ORDER BY SUM(Votes.BountyAmount) DESC) AS Rank,
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)
INNER JOIN Posts p ON p.ID = v.PostID
WHERE p.OwnerUserID = Users.Id AND p.CommunityOwnedDate IS NULL
) AS [Total Rep],
'~' + CAST(CAST(CAST(SUM(Votes.BountyAmount) AS Decimal) / CAST(Users.Reputation AS Decimal) * 100.00 AS Decimal) AS varchar(MAX)) + '%' AS [% Current Rep],
'~' + CAST(CAST(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)
INNER JOIN Posts p ON p.ID = v.PostID
WHERE p.OwnerUserID = Users.Id AND p.CommunityOwnedDate IS NULL
) AS Decimal) / CAST(Users.Reputation AS Decimal) * 100.00 AS Decimal) AS varchar(MAX)) + '%' AS [% Total Rep]