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

-- ((positive + 1.9208) / (positive + negative) - 
--  1.96 * SQRT((positive * negative) / (positive + negative) + 0.9604) / 
--         (positive + negative)) / (1 + 3.8416 / (positive + negative))

DECLARE @SQLString1 nvarchar(MAX);
DECLARE @SQLString2 nvarchar(MAX);
DECLARE @SQLSubStringQ nvarchar(2000);
DECLARE @SQLSubStringA nvarchar(2000);
DECLARE @positive nvarchar(100) = 'CAST(SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END) AS numeric(19, 9))';
DECLARE @negative nvarchar(100) = 'CAST(SUM(CASE WHEN 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 Votes.PostId = Posts.Id 
                          WHERE Posts.Id = ANY (SELECT a.PostId FROM PostTags a WHERE a.TagId = PostTags.TagId)
                          HAVING ' + @positive + ' + ' + @negative + ' > 0';
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 Votes.PostId = Posts.Id 
                          WHERE Posts.ParentId = ANY (SELECT a.PostId FROM PostTags a WHERE a.TagId = PostTags.TagId)
                          HAVING ' + @positive + ' + ' + @negative + ' > 0';
SET     @SQLString1 = 'SELECT Tags.TagName,
                       Tags.Count AS TagQCount,
                       Sum(Posts.Score) AS TagQScore,
                       (' + @SQLSubStringQ + ') AS TagQRating,
                       Sum(Posts.AnswerCount) AS TagACount,
                       (
                         SELECT COALESCE(SUM(b.Score), 0) FROM Posts b WHERE b.ParentId = ANY 
                         (SELECT c.PostId FROM PostTags c WHERE c.TagId = PostTags.TagId)
                       ) AS TagAScore,
                       (' + @SQLSubStringA + ') AS TagARating '
SET     @SQLString2 = 'FROM Tags
                       INNER JOIN PostTags
                       ON Tags.Id = PostTags.TagId
                       INNER JOIN Posts
                       ON Posts.Id = PostTags.PostId
                       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(@SQLString1 + @SQLString2);

Enter Parameters

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