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.

Q&A about the site for writers/artists using science, geography and culture to construct imaginary worlds and settings

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

fork query

created aug 2 11 Jon Seigel