Total Number of Posted Answer and Accepted Answer to a certain user

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- This query will calculate the total number of posted answers
--      and the total number of accepted answers to a certain user.

-- It requires three parameters:
--      @UserID               =>  'Your' UserID
--      @MinimumAnswerCount   =>  minimum number of posted answer
--      @MaximumAnswerCount   =>  maximum number of posted answer

-- To counter check if the result is correct, please visit
-- the URL below to get the Links of the question

-- http://data.stackexchange.com/stackoverflow/query/105580/where-did-we-meet


DECLARE @UserID int = ##UserId##
DECLARE @MinimumAnswerCount int = ##MinimumAnswerCount##
DECLARE @MaximumAnswerCount int = ##MaximumAnswerCount##

SELECT  b.OwnerUserId AS [User Link],
        COUNT(*) PostedAnswer,
        COUNT(CASE WHEN b.AcceptedAnswerId = a.ID THEN 1 END) AS AcceptedAnswer,
        CASE 
            WHEN COUNT(*) = COUNT(CASE WHEN b.AcceptedAnswerId = a.ID THEN 1 END)
            THEN 'Perfect!'
            ELSE ''
        END [Status]
FROM	Posts a
		INNER JOIN Posts b
			ON a.ParentID = b.ID
WHERE   a.PostTypeId = 2 AND
		a.OwnerUserID = @UserID AND
        b.OwnerUserId <> ''
GROUP   BY b.OwnerUserId
HAVING  COUNT(*) BETWEEN @MinimumAnswerCount AND @MaximumAnswerCount
ORDER   BY  PostedAnswer DESC, 
            AcceptedAnswer DESC,
            [User Link] ASC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached