0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows what percentage of a user's answers were accepted. Self-accepts are included separately.

Q&A for people looking to bypass life's everyday problems with simple tricks

-- Answer Success Rate
-- Shows what percentage of a user's answers were accepted.
-- Self-accepts are included seperately.

DECLARE @UserId         INT = ##UserId##;
DECLARE @SelfAnswered   INT,
        @OthersAccepted INT,
        @TotalAnswers   INT;

SELECT
    @SelfAnswered   = SUM ( CASE Qst.AcceptedAnswerId
                                WHEN ans.Id  THEN
                                    CASE Qst.OwnerUserId
                                        WHEN @UserId THEN   1
                                        ELSE                0
                                    END
                                ELSE    0
                            END
                    ),
    @OthersAccepted = SUM ( CASE Qst.AcceptedAnswerId
                                WHEN ans.Id  THEN
                                    CASE Qst.OwnerUserId
                                        WHEN @UserId THEN   0
                                        ELSE                1
                                    END
                                ELSE    0
                            END
                    ),

    @TotalAnswers   = COUNT (ans.Id)

FROM        Posts ans
INNER JOIN  Posts Qst
ON          ans.ParentId            = Qst.Id
WHERE       ans.OwnerUserId         = @UserId


DECLARE @Results TABLE (
    item        VARCHAR(60),
    [Count]     INT,
    [Percent]   FLOAT
)
INSERT INTO
    @Results (item, [Count], [Percent])
VALUES
    ('Total Answers   ', @TotalAnswers,     ROUND (100.01, 1)                                   ),
    ('Others Accepted ', @OthersAccepted,   ROUND (@OthersAccepted  * 100.0 / @TotalAnswers, 1) ),
    ('Self Answered   ', @SelfAnswered,     ROUND (@SelfAnswered    * 100.0 / @TotalAnswers, 1) )

SELECT * FROM @Results

fork query

created aug 11 11 Awesome Poodles