Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation
WITH Raw AS ( SELECT MIN(Posts.OwnerUserId) AS UserId, 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 WHERE Votes.PostId = Posts.Id AND Votes.VoteTypeId BETWEEN 2 AND 3 AND Posts.OwnerUserId > 0 GROUP BY Posts.Id ) SELECT TOP 1000 UserId AS [User Link], SUM(Ups) AS TotalUps, SUM(Downs) AS TotalDowns, 100.0 * SUM(Ups) / (SUM(Ups) + SUM(Downs)) AS UpPercent, SUM(CASE WHEN Ups > Downs THEN Downs ELSE Ups END) AS Controversity FROM Raw GROUP BY UserId ORDER BY Controversity DESC, UserId