Most Upvoted Questions In A Tag

1

Please login or register to vote for this query.

(click on this box to dismiss)

Science Fiction and Fantasy

Q&A for science fiction and fantasy enthusiasts

-- Most Upvoted Questions In A Tag
-- Enter your UserID to see if you've answered that question
-- Enter MaxAnswer to filter out the questions that have a lot of answers already

-- Returns:
-- Answered: If the user's answered that question
-- QaRatio: The score ratio of the top score Answer to the Question. If the
--  ratio is 1.5 and the answer's score is 150, then the question's score is 100.
--  If the score is
--  - > 1.5: the answer is good enough, it may be obselete though, so make sure
--           to skim it first.
--  - 1 to 1.2: the answer barely solves the problem
--  - < 1: the answer is garbage/obselete or the question is unsolvable

DECLARE @UserID int = ##UserID?9449426##
DECLARE @TagName nvarchar(max) = '##TagName##'
DECLARE @MaxAnswer INT = ##MaxAnswer?100000##
DECLARE @Tick NCHAR = NCHAR(9989)
DECLARE @Untick NCHAR = NCHAR(10060)

SELECT TOP 3000
  q.Id,
  q.Score AS QScore,
  q.AnswerCount,
  MAX(a.Score) AS AMaxScore
INTO #Questions
FROM PostTags
  INNER JOIN Posts q on PostTags.PostId = q.Id
  INNER JOIN Tags on PostTags.TagId = Tags.Id
  LEFT JOIN Posts a ON a.ParentId = q.Id
WHERE q.PostTypeId = 1
  AND Tags.TagName = @TagName
  AND q.AnswerCount <= @MaxAnswer
GROUP BY q.Id, q.Score, q.AnswerCount
ORDER BY q.Score DESC

SELECT *
INTO #MyAnswers
FROM Posts
WHERE OwnerUserId = @UserID
  AND PostTypeId = 2

SELECT q.Id AS [Post Link],
       q.AnswerCount,
       q.QScore,
       q.AMaxScore AS AScore,
       ROUND(q.AMaxScore * 1.0 / q.QScore, 2, 1) AS QaRatio,
       IIF(a.ID IS NULL, @Untick, @Tick) AS Answered
FROM #Questions q
LEFT JOIN #MyAnswers a ON a.ParentId = q.Id
ORDER BY q.QScore DESC

--SELECT * FROM #Questions

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results