0

Please login or register to vote for this query.

(click on this box to dismiss)

Displays the questions with the greatest ratio of upvotes to views, for questions with at least a given number of views. This helps to find the true gems, where looking at just upvotes tends to favour questions that have hit the Top Network Questions and received a ton of views.

Q&A for those who create, solve, and study puzzles

-- Ratio of upvotes to views received.

-- Displays the questions with the greatest ratio of upvotes to views,
-- for questions with at least a given number of views.  This helps to
-- find the true gems, where looking at just upvotes tends to favour
-- questions that have hit the Top Network Questions and received a ton of views.

-- Only consider questions with more votes/views than this
DECLARE @VotesThreshold int = ##MinimumVotes?10##
DECLARE @ViewsThreshold int = ##MinimumViews?300##

SELECT TOP 100
    -- Get the rank, based on the same ordering used for the vote-to-view percentage
    row_number() OVER(ORDER BY CAST(p.Score AS float) / p.ViewCount DESC)
      AS [Rank],

    p.Id AS [Post Link],
    p.OwnerUserId AS [User Link],
    p.Score,
    p.ViewCount AS [Views],
    
    -- Vote-to-view percentage (see explanation above)
    ROUND(CAST(p.Score AS float) / p.ViewCount * 100, 4)
       AS [Vote-to-view Percentage],

    p.CreationDate
    
FROM
    Posts p 
WHERE 
    p.ViewCount > @ViewsThreshold
    AND p.Score > @VotesThreshold
    AND p.Score > 0

ORDER BY
    [Vote-to-view Percentage] DESC

fork query

edited jan 11 16

created jan 8 16 GentlePurpleRain