Most controversial users of the site

1

Please login or register to vote for this query.

(click on this box to dismiss)

Controversity: SUM(MIN(Downs, Ups) per post) / Reputation

Sitecore

Q&A for developers and end users of the Sitecore CMS and multichannel marketing software

WITH Raw AS (
  SELECT
    Posts.Id,
    MIN(Posts.OwnerUserId) AS UserId,
    MIN(Users.Reputation) AS UserRep,
    COUNT(CASE WHEN Votes.VoteTypeId = 2 THEN 1 END) AS Ups,
    COUNT(CASE WHEN Votes.VoteTypeId = 3 THEN 1 END) AS Downs
  FROM Posts, Votes, Users
  WHERE
    Posts.Id = Votes.PostId
    AND
    Votes.VoteTypeId BETWEEN 2 AND 3
    AND
    Posts.OwnerUserId > 0
    AND
    Users.Id = Posts.OwnerUserId
  GROUP BY Posts.Id
  HAVING MIN(Users.Reputation) > 1
),
Raw2 AS (
  SELECT
    UserId,
    MIN(UserRep) AS UserRep,
    1.0 * SUM(CASE WHEN Ups > Downs THEN Downs ELSE Ups END)
      / MIN(UserRep) AS Controversity
  FROM Raw
  GROUP BY UserId
),
Raw3 AS (
  SELECT
    DENSE_RANK() OVER (ORDER BY Controversity DESC) AS Rank,
    UserId,
    Controversity
  FROM Raw2
)
SELECT
  Rank,
  UserId AS [User Link],
  Controversity
FROM Raw3
WHERE Rank <= 100
ORDER BY Rank, UserId

Enter Parameters

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