Refining criteria for a Revival/Necromancer gold badge

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- Refining criteria for a Revival/Necromancer gold badge
-- Proposed at http://meta.stackexchange.com/a/69118/, http://meta.stackexchange.com/q/97455/
-- Query mix of http://meta.stackexchange.com/a/240302/, /stackoverflow/query/39389/, /stackoverflow/query/229333/, /stackoverflow/query/265610/

DECLARE @DaysOld int = ##DaysOld##
DECLARE @MinScore int = ##MinScore##
DECLARE @MinNumber int = ##MinNumber##

SELECT AOwnerId [User Link], Count
FROM (SELECT a.OwnerUserId AOwnerId,
             count(*) Count
        FROM Posts q
        JOIN Posts AS a
        ON a.ParentId = q.Id
        WHERE q.PostTypeId = 1
        -- The questions are not closed
        AND q.ClosedDate is NULL
        AND a.PostTypeId = 2
        -- Exclude self answered or wiki
        AND IsNull(q.OwnerUserId, -1) <> a.OwnerUserId
        AND a.CommunityOwnedDate is NULL
        -- Answered over N days after question
        AND abs(datediff(day, q.CreationDate, a.CreationDate)) >= @DaysOld
        -- The answer hit the mark: either it was accepted...
        AND ( q.AcceptedAnswerId = a.Id
        -- ...or scored well on unanswered question...
        OR ( a.Score >= @MinScore
             AND q.AcceptedAnswerId is NULL )
        -- ...or it came first and was ok.
        OR ( a.Score >= 1
             AND a.Id = ( SELECT min(a2.Id)
                                   FROM Posts q2
                                   JOIN Posts a2
                                   ON a2.ParentId = q2.Id
                                   AND q2.Id = q.Id )
           )
        )

        GROUP BY a.OwnerUserId
        HAVING count(*) >= @MinNumber ) Answerer

Enter Parameters

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