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,
       (
       SELECT ((COALESCE(SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END), 0) + 1.9208) / SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END)
       - 1.96 * SQRT(COALESCE(SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END), 0) * COALESCE(SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END), 0) / CAST(SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END) AS NUMERIC(9, 4)) + 0.9604) / 
       SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END)) / (1 + 3.8416 / SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END))FROM Posts a
         INNER JOIN Votes v ON a.Id = v.PostId
         WHERE a.Id =
         ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
         HAVING SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END) > 0
       ) AS NetQuestionScore,
       Sum(Posts.AnswerCount) AS AnswerCount,
       (
       SELECT ((SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END) + 1.9208) / SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END)
       - 1.96 * SQRT(SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END) * SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END) / CAST(SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END) AS NUMERIC(9, 4)) + 0.9604) / 
       SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END)) / (1 + 3.8416 / SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END))FROM Posts a
         INNER JOIN Votes v ON a.Id = v.PostId
         WHERE a.ParentId =
         ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
         HAVING SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END) > 0
       ) AS NetAnswerScore,
       Tags.Count + Sum(Posts.AnswerCount) AS PostCount,
       (
       SELECT ((COALESCE(SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END), 0) + 1.9208) / SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END)
       - 1.96 * SQRT(COALESCE(SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END), 0) * COALESCE(SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END), 0) / CAST(SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END) AS NUMERIC(9, 4)) + 0.9604) / 
       SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END)) / (1 + 3.8416 / SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END))FROM Posts a
         INNER JOIN Votes v ON a.Id = v.PostId
         WHERE a.ParentId =
         ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
         OR a.Id =
         ANY(SELECT Id FROM Posts b WHERE b.Tags LIKE '%' + Tags.TagName + '%')
         HAVING SUM(CASE WHEN VoteTypeId IN (1, 2, 3) THEN 1 ELSE 0 END) > 0
       ) AS NetPostScore
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