Q&A for those interested in the geology, meteorology, oceanography, and environmental sciences
-- Questions with Most Answers by Tag -- Shows the number of questions with the most answers by tag for the most popular tags. DECLARE @minQuestionCount int = 500 DECLARE @topN int = 50 -- Order by rankings SELECT TagName, QuestionCount, AnsweredCount, AnsweredCountRank, AnsweredPct, AnsweredPctRank FROM ( -- Add rankings SELECT *, RANK() OVER (ORDER BY AnsweredCount DESC) AS AnsweredCountRank, RANK() OVER (ORDER BY AnsweredPct DESC) AS AnsweredPctRank FROM ( -- For top n most populous tags SELECT TOP (@topN) *, CAST((100.0 * a.AnsweredCount / (1.0 * a.QuestionCount)) AS decimal(18, 2)) AS AnsweredPct FROM ( -- All answered questions grouped by tag SELECT t.TagName AS TagName, COUNT(q.Id) AS AnsweredCount, t.Count AS QuestionCount FROM Posts q INNER JOIN PostTags pt ON q.Id = pt.PostId INNER JOIN Tags t ON pt.TagId = t.Id WHERE (q.PostTypeId = 1) -- Only question posts AND (q.ClosedDate IS NULL) -- Ignore closed questions AND ISNULL(q.AnswerCount, 0) > 0 -- Only answered questions GROUP BY TagName, t.Count ) a WHERE a.QuestionCount > @minQuestionCount ORDER BY a.QuestionCount DESC ) b ) c ORDER BY (AnsweredCount / AnsweredPctRank) DESC, AnsweredPctRank ASC, AnsweredCountRank ASC;