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

Q&A for anime and manga fans

DECLARE @SQLString nvarchar(MAX);
DECLARE @SQLSubStringQ nvarchar(2000);
DECLARE @SQLSubStringA nvarchar(2000);
DECLARE @positive nvarchar(100) = 'CAST(SUM(CASE WHEN Votes.VoteTypeId = 2 THEN 1 ELSE 0 END) AS numeric(19, 9))';
DECLARE @negative nvarchar(100) = 'CAST(SUM(CASE WHEN Votes.VoteTypeId = 3 THEN 1 ELSE 0 END) AS numeric(19, 9))';
SET     @SQLSubStringQ = 'SELECT ((' + @positive + ' + ' + '1.9208) / (' + @positive + ' + ' + @negative + ') -
                                  1.96 * SQRT((' + @positive + ' * ' + @negative + ') / (' + @positive + ' + ' + @negative + ') + 0.9604) / 
                                         (' + @positive + ' + ' + @negative + ')) / (1 + 3.8416 / (' + @positive + ' + ' + @negative + '))
                          FROM Posts
                          INNER JOIN Votes
                          ON Posts.Id = Votes.PostId
                          WHERE Posts.Id = ANY (SELECT a.PostId FROM PostTags a WHERE a.TagId = PostTags.TagId)
                          HAVING ' + @positive + ' + ' + @negative + ' > 0';
SET     @negative = 'CAST(SUM(CASE WHEN Votes.VoteTypeId = 3 THEN 1.5 ELSE 0 END) AS numeric(19, 9))';
SET     @SQLSubStringA = 'SELECT ((' + @positive + ' + ' + '1.9208) / (' + @positive + ' + ' + @negative + ') -
                                  1.96 * SQRT((' + @positive + ' * ' + @negative + ') / (' + @positive + ' + ' + @negative + ') + 0.9604) / 
                                         (' + @positive + ' + ' + @negative + ')) / (1 + 3.8416 / (' + @positive + ' + ' + @negative + '))
                          FROM Posts
                          INNER JOIN Votes
                          ON Posts.Id = Votes.PostId
                          WHERE Posts.ParentId = ANY (SELECT a.PostId FROM PostTags a WHERE a.TagId = PostTags.TagId)
                          HAVING ' + @positive + ' + ' + @negative + ' > 0';
SET     @SQLString = CAST('' as nvarchar(MAX)) + -- Workaround: https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated/17785175#17785175
                      'SELECT Tags.TagName,
                       Tags.Count AS QuestionCount,
                       Sum(Posts.Score) AS NetQuestionScore,
                       (' + @SQLSubStringQ + ') AS QuestionRatings,
                       Sum(Posts.AnswerCount) AS AnswerCount,
                       (
                         SELECT COALESCE(SUM(a.Score), 0) FROM Posts a WHERE a.ParentId = ANY 
                         (SELECT b.PostId FROM PostTags b WHERE b.TagId = PostTags.TagId)
                       ) AS NetAnswerScore,
                       (' + @SQLSubStringA + ') AS AnswerRatings
                       FROM Tags
                       INNER JOIN PostTags
                       ON Tags.Id = PostTags.TagId
                       INNER JOIN Posts
                       ON PostTags.PostId = Posts.Id
                       AND Posts.PostTypeId IN (1, 2, 3)
                       WHERE (' + @SQLSubStringQ + ') IS NOT NULL OR (' + @SQLSubStringA + ') IS NOT NULL
                       GROUP BY PostTags.TagId, Tags.TagName, Tags.Count
                       ORDER BY Tags.TagName ASC';
EXECUTE(@SQLString);

view current version

edited aug 17 17 Gao

created nov 14 16 anonymous