Bounty statistics v0.3

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #results (QuestionId int, BountyStart datetime, Asker int,
  Bountier int, NumberOfAnswers int, NumberOfUpvotedAnswers int,
  AcceptedAnswer int);
  
INSERT INTO #results
SELECT QuestionId, 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

SELECT '# of bounties', COUNT(*)
  FROM #results UNION
SELECT '% with accepted answers',
  ROUND(AVG(CAST(AcceptedAnswer AS FLOAT)) * 100, 2)
  FROM #results UNION  
SELECT '% with accepted answer (bounty by OP)',
  ROUND(SUM(CASE WHEN Asker = Bountier THEN AcceptedAnswer ELSE 0 END) * 100.0 /
        SUM(CASE WHEN Asker = Bountier THEN 1 ELSE 0 END), 2)
  FROM #results UNION
SELECT '% with any answers',
  ROUND(AVG(CASE WHEN NumberOfAnswers > 0 THEN 1.0 ELSE 0.0 END) * 100, 2)
  FROM #results UNION
SELECT 'Average # of answers',
  ROUND(AVG(CAST(NumberOfAnswers AS FLOAT)), 2)
  FROM #results UNION
SELECT 'Median # of answers',
  CAST(
   (SELECT MAX(NumberOfAnswers) FROM
     (SELECT TOP 50 PERCENT NumberOfAnswers FROM #Results ORDER BY NumberOfAnswers) AS BottomHalf)
   +
   (SELECT MIN(NumberOfAnswers) FROM
     (SELECT TOP 50 PERCENT NumberOfAnswers FROM #Results ORDER BY NumberOfAnswers DESC) AS TopHalf)
  AS FLOAT) / 2.0
  FROM #results UNION
SELECT 'Modal # of answers',
  (SELECT TOP 1 NumberOfAnswers
  FROM #results GROUP BY NumberOfAnswers ORDER BY COUNT(*) DESC) UNION
SELECT 'Maximum # of answers',
  MAX(NumberOfAnswers)
  FROM #results UNION
SELECT '% with any upvoted answers',
  ROUND(AVG(CASE WHEN NumberOfUpvotedAnswers > 0 THEN 1.0 ELSE 0.0 END) * 100, 2)
  FROM #results UNION
SELECT 'Average # of upvoted answers',
  ROUND(AVG(CAST(NumberOfUpvotedAnswers AS FLOAT)), 2)
  FROM #results UNION
SELECT 'Median # of upvoted answers',
  CAST(
   (SELECT MAX(NumberOfUpvotedAnswers) FROM
     (SELECT TOP 50 PERCENT NumberOfUpvotedAnswers FROM #Results ORDER BY NumberOfUpvotedAnswers) AS BottomHalf)
   +
   (SELECT MIN(NumberOfUpvotedAnswers) FROM
     (SELECT TOP 50 PERCENT NumberOfUpvotedAnswers FROM #Results ORDER BY NumberOfUpvotedAnswers DESC) AS TopHalf)
  AS FLOAT) / 2.0
  FROM #results UNION
SELECT 'Modal # of upvoted answers',
  (SELECT TOP 1 NumberOfUpvotedAnswers
  FROM #results GROUP BY NumberOfUpvotedAnswers ORDER BY COUNT(*) DESC) UNION
SELECT 'Maximum # of upvoted answers',
  MAX(NumberOfUpvotedAnswers)
  FROM #results;

  
  
DROP TABLE #results;

Enter Parameters

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