BountifulII

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

Statistical Analysis

Q&A for people interested in statistics, machine learning, data analysis, data mining, and data visualization

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

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results