Unanswered Questions by Tag

16

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the number of unanswered questions by tag for the most popular tags.

Reverse Engineering Meta

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

-- 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

Enter Parameters

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