Most controversial users of a site, v2

0

Please login or register to vote for this query.

(click on this box to dismiss)

Esperanto Language

Q&A for teachers and students of the Esperanto language

WITH UserVotes AS (
  SELECT
    Users.Id AS UserId,
    MIN(Users.Reputation) AS Reputation,
    Votes.VoteTypeId,
    COUNT(*) AS N
  FROM Users
  LEFT JOIN Posts ON Posts.OwnerUserId = Users.Id
  LEFT JOIN Votes ON Votes.PostId = Posts.Id
  WHERE Votes.VoteTypeId IN (2, 3)
  GROUP BY Users.Id, Votes.VoteTypeId
),
UserUpDown AS (
  SELECT
    Up.UserId,
    Up.Reputation,
    Up.N AS UpVotes,
    Down.N AS DownVotes,
    1.0 * Down.N / (Up.N + Down.N) AS DownRatio
  FROM UserVotes AS Up, UserVotes AS Down
  WHERE
    Up.UserId = Down.UserId
    AND Up.VoteTypeId = 2
    AND Down.VoteTypeId = 3
    AND Down.N > 0
    AND Up.N > 0
),
RawResult AS (
  SELECT
    DENSE_RANK() OVER (ORDER BY Reputation DESC) AS ReputationRank,
    DENSE_RANK() OVER (ORDER BY DownRatio DESC) AS DownRatioRank,
    DENSE_RANK() OVER (ORDER BY DownVotes DESC) AS DownVoteRank,
    *
  FROM UserUpDown
)
SELECT TOP 100
  UserId AS [User Link],
  ReputationRank + DownVoteRank + DownRatioRank AS ControversityRank,
  *
FROM RawResult
ORDER BY ControversityRank;

Enter Parameters

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