Shows the number of unanswered questions by tag for the most popular tags.
Q&A about the site for serious players and enthusiasts of chess
-- Unanswered Questions by Tag -- Shows the number of unanswered questions by tag for the most popular tags. DECLARE @minQuestionCount int = 500 DECLARE @topN int = 50 -- Order by rankings SELECT TagName, QuestionCount, UnansweredCount, UnansweredCountRank, UnansweredPct, UnansweredPctRank FROM ( -- Add rankings SELECT *, RANK() OVER (ORDER BY UnansweredCount DESC) AS UnansweredCountRank, RANK() OVER (ORDER BY UnansweredPct DESC) AS UnansweredPctRank FROM ( -- For top n most populous tags SELECT TOP (@topN) *, CAST((100.0 * a.UnansweredCount / (1.0 * a.QuestionCount)) AS decimal(18, 2)) AS UnansweredPct FROM ( -- All unanswered questions grouped by tag SELECT t.TagName AS TagName, COUNT(1) AS UnansweredCount, 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) AND (q.ClosedDate IS NULL) AND NOT EXISTS ( SELECT * FROM Posts a WHERE (a.ParentId = q.Id) AND ((a.Score > 0) OR (a.Id = q.AcceptedAnswerId)) ) GROUP BY TagName, t.Count ) a WHERE a.QuestionCount > @minQuestionCount ORDER BY a.QuestionCount DESC ) b ) c ORDER BY (UnansweredCount / UnansweredPctRank) DESC, UnansweredPctRank ASC, UnansweredCountRank ASC