Tags Upvote/Downvote

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

DECLARE @tag nvarchar(35) = ##tag:string?<null>##
DECLARE @months int = ##months:int?12##
DECLARE @minCount int = ##minCount:int?500##

IF @tag = '<null>' SET @tag = NULL

SELECT
  q.TagName,
  q.UpMods - q.DownMods as [Sum],
  q.UpMods,
  q.DownMods,
  CAST(q.DownMods AS DECIMAL) / CAST(q.UpMods + q.DownMods AS DECIMAL) * 100 as PercentDownMods
FROM (
  SELECT
    Tags.TagName,
    SUM(CASE WHEN Votes.VoteTypeId = 2 THEN 1 ELSE 0 END) as UpMods,
    SUM(CASE WHEN Votes.VoteTypeId = 3 THEN 1 ELSE 0 END) as DownMods
  FROM
    Posts
  JOIN
    PostTags ON PostTags.PostId = Posts.Id
  JOIN
    Tags ON PostTags.TagId = Tags.Id
  JOIN
    Votes ON Votes.PostId = Posts.Id
  WHERE
    (@tag IS NULL OR Tags.TagName = @tag)
    AND
    (
      Votes.VoteTypeId = 2
      OR
      Votes.VoteTypeId = 3
    )
    AND
    Posts.CreationDate > DATEADD(MONTH, -1 * (@months), GETDATE())
  GROUP BY
    Tags.TagName
  HAVING COUNT(Posts.Id) > @minCount
) as q
ORDER BY [Sum]

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results