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