0

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited mar 20

created mar 16 Dale.Morrell