Q&A about the site for administrators, end users, developers and designers for Craft CMS
-- Compared the original, this doesn't fail if the number
-- of upvotes is zero (e.g., seen when running it on
-- Stack Overflow):
-- "Line 1: Divide by zero error encountered. The statement
-- has been terminated."
-- Though the ratio is no longer strictly correct in the
-- absolute sense. But the sorting is not affected. That
-- would probably be trivial to fix, e.g. by excluding
-- users with zero upvotes.
-- It also:
-- 1. Limits the output to 150 (instead of
-- about 50,000 on Stack Overflow).
-- 2. Rounds the ratio to reasonable number of
-- significant digits
-- 3. Expands "Desde" (could have been a Spanish word)
over (ORDER BY Downvotes DESC, Upvotes ASC, DisplayName, Id)
Id as [User],
((Cast(Downvotes as float)/(Upvotes+1))) as Ratio,
LastAccessDate as [Last access date],
CONVERT(varchar(10), CreationDate, 103) as "From"
WHERE Id <> -1 and Downvotes >= 10
SELECT TOP 150 *
ORDER BY Rank
SELECT Rank, Downvotes
ORDER BY Ratio DESC