Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games
DECLARE @total float SELECT @total = Sum(Cast(ViewCount as bigint)) FROM Posts WHERE PostTypeId = 1 DECLARE @dv TABLE (PostId int, DownVotes float, UpVotes float) INSERT INTO @dv SELECT Posts.Id, Cast(SUM(CASE VoteTypeId WHEN 3 THEN 1 ELSE 0 END) AS int) AS DownVotes, Cast(SUM(CASE VoteTypeId WHEN 2 THEN 1 ELSE 0 END) AS int) AS UpVotes FROM Posts LEFT OUTER JOIN Votes ON Posts.Id=Votes.PostId WHERE PostTypeId=1 GROUP BY Posts.Id DECLARE @DownMedian float DECLARE @DownMean float DECLARE @DownMax float DECLARE @UpMedian float DECLARE @UpMean float DECLARE @UpMax float SELECT @DownMedian=(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DownVotes) OVER()), @UpMedian=(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UpVotes) OVER()) FROM @dv SELECT @DownMean=str(AVG(DownVotes),6,2), @DownMax=str(MAX(DownVotes),6,2), @UpMean=str(AVG(UpVotes),6,2), @UpMax=str(MAX(UpVotes),6,2) FROM @dv SELECT ROW_NUMBER() OVER (ORDER BY cast(Count(CASE Downvotes WHEN 0 THEN NULL ELSE 1 END) as float)/ Count(*)) AS Rank, Tags.TagName as Tag, Count(*) AS TotalQuestions, Count(CASE Downvotes WHEN 0 THEN NULL ELSE 1 END) AS DownVotedQuestions, str(cast(Count(CASE Downvotes WHEN 0 THEN NULL ELSE 1 END) as float)*100/ Count(*),6,2)+'%' AS DownVotedRate, str(Avg(DownVotes),6,2) AS DownTagMean, @DownMean AS DownTotalMean, DownMedian AS DownTagMedian, @DownMedian AS DownTotalMedian, Max(DownVotes) AS DownTagMax, @DownMax as DownTotalMax, Count(CASE Upvotes WHEN 0 THEN NULL ELSE 1 END) AS UpVotedQuestions, str(cast(Count(CASE Upvotes WHEN 0 THEN NULL ELSE 1 END) as float)*100/ Count(*),6,2)+'%' AS UpVotedRate, str(Avg(UpVotes),6,2) AS UpTagMean, @UpMean AS UpTotalMean, UpMedian AS UpTagMedian, @UpMedian AS UpTotalMedian, Max(UpVotes) AS UpTagMax, @UpMax as UpTotalMax, Count(CASE (DownVotes+UpVotes) WHEN 0 THEN 1 ELSE NULL END) as NoVotedQUestions, str(cast(Count(CASE (Downvotes+UpVotes) WHEN 0 THEN 1 ELSE NULL END) as float)*100/ Count(*),6,2)+'%' AS NoVotedRate FROM @dv AS DV JOIN PostTags ON DV.PostId = PostTags.PostId JOIN Tags ON PostTags.TagId = Tags.Id JOIN (SELECT DISTINCT Tags.Tagname, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DownVotes) OVER( PARTITION BY Tags.Tagname) AS DownMedian, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY UpVotes) OVER( PARTITION BY Tags.Tagname) AS UpMedian FROM @dv AS DV JOIN PostTags ON DV.PostId = PostTags.PostId JOIN Tags ON PostTags.TagId = Tags.Id) AS M ON Tags.Tagname = M.Tagname GROUP BY Tags.TagName, M.DownMedian, M.UpMedian HAVING Count(*) >=50 ORDER BY Tags.TagName ASC