0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT
  bounty_winner_user_id,
  bounty_winner.DisplayName AS bounty_winner,
  bounty_creator_user_id,
  bounty_creator.DisplayName AS bounty_creator,
  COUNT(*) AS number_of_bounties,
  SUM(bounty_data.bounty_end_amount) AS total_rep_won
FROM (
  SELECT answer.OwnerUserId AS bounty_winner_user_id,
    bounty_start.UserId AS bounty_creator_user_id,
    question.Id AS question_id,
    bounty_start.BountyAmount AS bounty_start_amount,
    bounty_end.BountyAmount AS bounty_end_amount
  FROM Votes AS bounty_end
  INNER JOIN Posts AS answer ON answer.Id=bounty_end.PostId
  INNER JOIN Posts AS question ON question.Id=answer.ParentId
  INNER JOIN Votes AS bounty_start ON bounty_start.PostId=question.Id AND bounty_start.VoteTypeId=8
  WHERE bounty_end.VoteTypeId=9
) AS bounty_data
INNER JOIN Users AS bounty_winner ON bounty_winner.Id=bounty_data.bounty_winner_user_id
INNER JOIN Users AS bounty_creator ON bounty_creator.Id=bounty_data.bounty_creator_user_id
GROUP BY bounty_data.bounty_winner_user_id,
  bounty_winner.DisplayName,
  bounty_data.bounty_creator_user_id,
  bounty_creator.DisplayName
HAVING COUNT(*)>1
ORDER BY number_of_bounties DESC

fork query

edited mar 8 16

created mar 8 16 Mike D.