Most controversial users on the site

2

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- Controversity: MIN(downvotes,upvotes)
-- 
-- Factor: controversity/all votes the user got
--
-- Wow! I won this query on the Physics.Meta!

WITH UserVotes AS (
  SELECT Users.Id AS UserId, Votes.VoteTypeId, COUNT(*) AS N
  FROM Votes
  LEFT JOIN PostsWithDeleted ON Votes.PostId = PostsWithDeleted.Id
  LEFT JOIN Users ON PostsWithDeleted.OwnerUserId = Users.Id
  WHERE Votes.VoteTypeId IN (2,3)
  GROUP BY Users.Id, Votes.VoteTypeId
  HAVING COUNT(*) > ##MinimalVotes:INT?200##
  ),
MAIN AS (
  SELECT Up.UserId AS [User Link],
    CASE WHEN Up.N > Down.N THEN Down.N ELSE Up.N END AS Controversity,
    Up.N + Down.N AS AllVotes,
    Up.N AS Upvotes,
    Down.N AS Downvotes,
    CAST((CASE WHEN Up.N > Down.N THEN Down.N ELSE Up.N END) AS FLOAT)
     /
    CAST((Up.N + Down.N) AS FLOAT) AS Factor
  FROM UserVotes Up, UserVotes Down
  WHERE Up.UserId = Down.UserId
    AND Up.VoteTypeId = 2
    AND Down.VoteTypeId = 3)
SELECT *
FROM MAIN
ORDER BY Factor DESC;

Enter Parameters

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