Bounties sprouting many new answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT TOP 100 QuestionId AS [Post Link], BountyStart, Asker, Bountier,
  SUM(CASE WHEN AnswerId IS NOT NULL THEN 1 ELSE 0 END) AS NumberOfAnswers,
  SUM(CASE WHEN Score > 0 THEN 1 ELSE 0 END) AS NumberOfUpvotedAnswers,
  SUM(CASE WHEN AnswerId = AcceptedAnswerId THEN 1 ELSE 0 END) AS AcceptedAnswer
  FROM

(
SELECT q.Id AS QuestionId, q.AcceptedAnswerId, q.OwnerUserId AS Asker,
  pn.CreationDate AS BountyStart, pn.OwnerUserId AS Bountier,
  adb.Id AS AnswerId, adb.Score
  FROM Posts AS q
  INNER JOIN PostNotices AS pn ON pn.PostId = q.Id
                        AND pn.PostNoticeTypeId BETWEEN 10 AND 15 -- bounty notice
                        AND pn.DeletionDate IS NOT NULL
  LEFT OUTER JOIN Posts AS abb ON abb.ParentId = q.Id
                              AND abb.CreationDate < pn.CreationDate
  LEFT OUTER JOIN Posts AS adb ON adb.ParentId = q.Id
                              AND adb.CreationDate BETWEEN pn.CreationDate AND pn.DeletionDate
  WHERE abb.Id IS NULL -- no answer before bounty
) AS answers

GROUP BY QuestionId, BountyStart, Asker, Bountier
ORDER BY SUM(CASE WHEN AnswerId IS NOT NULL THEN 1 ELSE 0 END) DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results