2

Please login or register to vote for this query.

(click on this box to dismiss)

This report lists all the bounties offered and earned. It differentiates between bounties offered on questions that the bounty-supplier owns (Self) or does not own (Promotes).

Q&A for peer programmer code reviews

-- This report lists all the bounties offered and earned.
-- It differentiates between bounties offered on questions that
-- the bounty-supplier owns (Self) or does not own (Promotes).

WITH Bounties AS (
    SELECT
       UserID AS UserID,
       0 AS GetCount,
       0 AS GetAmount,
       COUNT(BountyAmount) AS GiveCount,
       SUM(BountyAmount) AS GiveAmount,
       SUM(case when Posts.OwnerUserId = Votes.UserId then 1 else 0 end) AS SelfCount,
       SUM(case when Posts.OwnerUserId = Votes.UserId then BountyAmount else 0 end) AS SelfAmount
    FROM Votes,
         Posts
    WHERE Votes.PostId = Posts.Id
      AND VoteTypeId = 8 -- Offered Bounty
    GROUP BY UserID
   UNION
    SELECT
       Posts.OwnerUserID AS UserID,
       COUNT(BountyAmount) AS GetCount,
       SUM(BountyAmount) AS GetAmount,
       0 AS GiveCount,
       0 AS GiveAmount,
       0 AS SelfCount,
       0 AS SelfAmount
    FROM Votes,
         Posts
    WHERE Votes.PostId = Posts.ID
      AND VoteTypeId = 9 -- Awarded Bounty
    GROUP BY Posts.OwnerUserID
  ) 

SELECT UserID AS [User Link],
       SUM(GiveCount - SelfCount) AS Pomotions,
       SUM(GiveAmount - SelfAmount) AS PromotedAmount,
       SUM(GetCount) AS GetCount,
       SUM(GetAmount) AS GetAmount,
       SUM(GiveCount) AS GiveCount,
       SUM(GiveAmount) AS GiveAmount,
       SUM(SelfCount) AS SelfCount,
       SUM(SelfAmount) AS SelfAmount,
       SUM(GiveCount + GetCount) AS EventCount,
       SUM(GetAmount - GiveAmount) AS NetBenefit
FROM Bounties
GROUP BY UserID
ORDER BY PromotedAmount DESC, EventCount DESC, NetBenefit DESC

fork query

edited jan 20 14

created jan 20 14 rolfl