List all Bounties by their question, with outcome (Awarded, Views, Num Answers, etc.)

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

WITH bountyCloseData AS (
    SELECT
                btyEnd.PostId
                , btyEnd.CreationDate   AS CloseDate
                , btyEnd.BountyAmount   AS [Awarded Rep]
                , CASE WHEN p.PostTypeId = 1  THEN  p.Id  ELSE  p.ParentId  END  AS questionId
    FROM        Votes btyEnd
    LEFT JOIN   Posts p                 ON p.ID = btyEnd.PostId
    WHERE       btyEnd.VoteTypeId       = 9
)
SELECT
            btyStart.PostId                                             AS [Post Link]
            , FORMAT (btyStart.CreationDate, 'yyyy-MM-dd')              AS [Open Dt]
            , FORMAT (bcd.CloseDate, 'yyyy-MM-dd')                      AS [Close Dt]
            , DATEDIFF (DAY, btyStart.CreationDate, bcd.CloseDate)      AS [Days Open]
            , btyStart.BountyAmount                                     AS [Offered Rep]
            , bcd.[Awarded Rep]
            , CASE WHEN p.AcceptedAnswerId IS NOT NULL THEN  'Y'  ELSE  'N'  END  AS [Has Accptd Ans?]
            , p.AnswerCount                                             AS [Num Answers]
            , p.ViewCount                                               AS [Views]
            , p.Score                                                   AS [Qstn Score]
FROM        Votes btyStart
LEFT JOIN   bountyCloseData bcd
ON (
    bcd.questionId = btyStart.PostId
    AND btyStart.CreationDate <= bcd.CloseDate
    AND DATEDIFF (DAY, btyStart.CreationDate, bcd.CloseDate) <= 8   -- 7 days plus grace period
    -- WARNING, Are there (rare) cases where a post can have more than 1 bounty in a 7 day period?
)
LEFT JOIN   Posts p                     ON p.ID = btyStart.PostId
WHERE       btyStart.VoteTypeId         = 8
ORDER BY    btyStart.PostId
            , btyStart.CreationDate

Enter Parameters

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