Average Accepted Answer Score: Jon Skeet, Everyone, Users Like Me, & Me

2

Please login or register to vote for this query.

(click on this box to dismiss)

Average score per answer of users similar to userId (answer count between 80% and 120% of userId's answer count) compared to userId, everyone, and Jon Skeet

Amateur Radio

Q&A for amateur radio enthusiasts

-- Average Accepted Answer Score: Jon Skeet, Everyone, Users Like Me, & Me
-- Average score per answer of users similar to userId
-- (answer count between 80% and 120% of userId's answer count)
-- compared to userId, everyone, and Jon Skeet

DECLARE @AnswerCount INT;
DECLARE @MinimunAnswers INT;
DECLARE @MaximumAnswers INT;
DECLARE @Scores TABLE
    (
    [Order] INT,
    [User] VARCHAR(50),
    Score DECIMAL(10,2)
    );

SELECT
    @AnswerCount = COUNT(*)

FROM
    Posts

WHERE
        Posts.PostTypeId = 2 --answers
    AND Posts.OwnerUserId = ##UserId## 

OPTION
    ( FORCE ORDER, LOOP JOIN )

SET @MinimunAnswers = @AnswerCount * .8
SET @MaximumAnswers = @AnswerCount * 1.2


INSERT INTO
    @Scores

SELECT
    1 AS [Order],
    'Jon Skeet' AS [User],
    ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score]

FROM
    Posts AS Answers

    INNER JOIN
    Posts AS Questions
    ON Answers.ParentId = Questions.Id

WHERE
        Answers.PostTypeId = 2 -- answer
    AND Questions.AcceptedAnswerId = Answers.Id -- accepted answer
    AND Answers.OwnerUserId = 22656 -- Jon Skeeet 

OPTION
    ( FORCE ORDER, LOOP JOIN )



INSERT INTO
    @Scores

SELECT
    2 AS [Order],
    'Everyone' AS [User],
    ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score]

FROM
    Posts AS Answers

    INNER JOIN
    Posts AS Questions
    ON Answers.ParentId = Questions.Id

WHERE
        Answers.PostTypeId = 2 -- answer
    AND Questions.AcceptedAnswerId = Answers.Id -- accepted answer

OPTION
    ( FORCE ORDER, LOOP JOIN )





INSERT INTO
    @Scores

SELECT
    3 AS [Order],
    'Everyone Like UserId: ' + CAST( ##UserId## AS VARCHAR ) AS [User],
    ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score]

FROM
    (
    SELECT
        Posts.Score,
        COUNT(*) AS AnswerCount

    FROM
        Posts

    WHERE
        Posts.PostTypeId = 2 --answer

    GROUP BY
        Posts.OwnerUserId,
        Posts.Score

    HAVING
        COUNT(*) BETWEEN @MinimunAnswers
                 AND     @MaximumAnswers

    ) AS Answers

OPTION
    ( FORCE ORDER, LOOP JOIN )



INSERT INTO
    @Scores

SELECT
    4 AS [Order],
    'UserId: ' + CAST( ##UserId## AS VARCHAR ) AS [User],
    ROUND( AVG( CAST( Answers.Score AS DECIMAL ) ), 2) AS [Average Answer Score]

FROM
    Posts AS Answers

    INNER JOIN
    Posts AS Questions
    ON Answers.ParentId = Questions.Id

WHERE
        Answers.PostTypeId = 2 -- answer
    AND Questions.AcceptedAnswerId = Answers.Id -- accepted answer
    AND Answers.OwnerUserId = ##UserId## 

OPTION
    ( FORCE ORDER, LOOP JOIN )




SELECT
    *
FROM
    @Scores
ORDER BY
    [Order]

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results