Post Scores per Tag

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
Questions AS
(
    SELECT Id, CAST(Score AS FLOAT) AS Score
    FROM Posts
    WHERE PostTypeId = 1
),
Answers AS
(
    SELECT ParentId, CAST(Score AS FLOAT) AS Score
    FROM Posts
    WHERE PostTypeId = 2
),
TaggedQuestions AS
(
    SELECT pt.TagId, AVG(q.Score) AS Score
    FROM Questions q
    INNER JOIN PostTags pt ON q.Id = pt.PostId
    GROUP BY pt.TagId
),
TaggedAnswers AS
(
    SELECT pt.TagId, AVG(a.Score) AS Score
    FROM Answers a
    INNER JOIN PostTags pt ON a.ParentId = pt.PostId
    GROUP BY pt.TagId
)
SELECT
    t.TagName,
    q.Score AS QuestionScore,
    a.Score AS AnswerScore
FROM Tags t
FULL OUTER JOIN TaggedQuestions q ON q.TagId = t.Id
FULL OUTER JOIN TaggedAnswers a ON a.TagId = t.Id
ORDER BY t.Count DESC

Enter Parameters

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