0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

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

fork query

edited nov 7 Oluwatosin

created nov 6 anonymous