Tag ratings in dynamic SQL

0

Please login or register to vote for this query.

(click on this box to dismiss)

New version: https://data.stackexchange.com/anime/query/745962/tag-ratings

Anime and Manga

Q&A for anime and manga fans

SELECT TagName,
       Tags.Count AS QuestionCount,
       Sum(Score) AS NetQuestionScore,
       Sum(Posts.AnswerCount) AS AnswerCount,
       (
         SELECT COALESCE(SUM(a.Score), 0) FROM Posts a WHERE a.ParentId =
         ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
       ) AS NetAnswerScore,
       Tags.Count + Sum(Posts.AnswerCount) AS PostCount,
       Sum(Score) + 
       (
         SELECT COALESCE(SUM(a.Score), 0) FROM Posts a WHERE a.ParentId =
         ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
       ) AS NetPostScore,
       CAST(
         (
           Sum(Score) +
           (
             SELECT COALESCE(SUM(a.Score), 0) FROM Posts a WHERE a.ParentId =
             ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
           )
         ) AS NUMERIC(9, 4)
       ) / (Tags.Count + Sum(Posts.AnswerCount)) AS AverageScorePerPost
FROM Tags
INNER JOIN Posts
ON Posts.Tags LIKE '%' + Tags.TagName + '%'
GROUP BY Tags.TagName, Tags.Count
ORDER BY PostCount DESC, NetPostScore DESC, TagName ASC

Enter Parameters

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