0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for information security professionals

-- 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 100 (instead of 
--      about 50,000 on Stack Overflow).
--
--   2. Rounds the ratio to a more reasonable 
--      number of significant digits
--
--   3. Expands "Desde" (could have been 
--      the Spanish word for "from")
--
--   4. Removed dead code/SQL
--
--   5. Used ISO-8601 format for the "from" column.
--

-- Other notes
--
--   1. The "Last access date" column, sorted on in reverse 
--      chronological order, gives a reasonable estimate 
--      for when the data in SEDE was last updated (better
--      than one hour).


SELECT
    row_number()
        over (ORDER BY Downvotes DESC, Upvotes DESC, DisplayName, Id)
        as Rank,
        Id as [User Link], -- "[User Link]" is a magic link 
                           -- (case-sensitive)...
    Downvotes,
    Upvotes,
    -- ((Cast(Downvotes as float)/(Upvotes+1))) as Ratio,
    round(((Cast(Downvotes as float)/(Upvotes+0.001))), 2) as Ratio,
    LastAccessDate as [Last access date],
    CreationDate as "From"
INTO #Ratio
FROM Users
WHERE Id <> -1 and Downvotes >= 500 and Upvotes >= 500

SELECT TOP 100 * 
FROM #Ratio
ORDER BY Ratio DESC

fork query

edited jul 31 anonymous

created jul 25 laptop2d