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(3600);
DECLARE @SQLSubStringA nvarchar(3600);
DECLARE @positive nvarchar(120) = 'SUM(CASE WHEN VoteTypeId IN (1, 2) THEN 1 ELSE 0 END)';
DECLARE @negative nvarchar(120) = 'SUM(CASE WHEN VoteTypeId = 3 THEN 1 ELSE 0 END)';
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 Id FROM Posts WHERE Posts.Tags LIKE ''%'' + Tags.TagName + ''%'')
                          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 Id FROM Posts WHERE Posts.Tags LIKE ''%'' + Tags.TagName + ''%'')
                          HAVING (' + @positive + ' + ' + @negative + ') > 0';
SET     @SQLString1 = 'SELECT TagName,
                       Tags.Count AS QuestionCount,
                       (' + @SQLSubStringQ + ') AS QuestionRating,
                       (' + @SQLSubStringA + ') AS AnswerRating '
SET     @SQLString2 = 'FROM Tags
                       INNER JOIN Posts
                       ON Posts.Tags LIKE ''%'' + Tags.TagName + ''%''
                       WHERE (' + @SQLSubStringQ + ') IS NOT NULL OR (' + @SQLSubStringA + ') IS NOT NULL
                       GROUP BY Tags.TagName, Tags.Count
                       ORDER BY TagName ASC';
EXECUTE(@SQLString1 + @SQLString2);

Enter Parameters

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