0

Please login or register to vote for this query.

(click on this box to dismiss)

This query shows a histogram of hours after offering a bounty and posting the answer which will eventually win the bounty

Q&A for SharePoint enthusiasts

-- How quick do I have to be to win a bounty?
-- This query shows a histogram of hours after offering a bounty
-- and posting the answer which will eventually win the bounty

SELECT TOP 100
       ROUND(CAST ((a.creationDate - bstart.creationDate) AS FLOAT)*24,0) as [Hours after offering a bounty until bounty-awarded answers is posted],
       COUNT(*) as [Count (*)],
       REPLICATE('=', COUNT(*)/10)

       -- q.id as [Post Link],
       -- CAST ((a.creationDate - q.creationDate) AS DECIMAL)*24,
       -- q.creationDate,
       -- a.creationDate,
       -- q.OwnerUserid,
       -- b.UserId

FROM posts q, 
     posts a,
     votes b,
     votes bstart
WHERE q.postTypeId = 1
AND   a.postTypeId = 2
AND   a.ParentId = q.Id
AND   b.voteTypeId = 9 -- bounty
AND   b.postId = a.id
AND   bstart.voteTypeId = 8 -- bountyEnd
AND   bstart.postId = q.id
AND   ROUND(CAST ((a.creationDate - bstart.creationDate) AS FLOAT),0) < 20 -- filtering too late bounties
AND   bstart.creationDate < a.creationDate

GROUP BY ROUND(CAST ((a.creationDate - bstart.creationDate) AS FLOAT)*24,0)

ORDER BY 1 ASC

fork query

created may 15 11 grzegorz.oledzki