Askers and Answerers to top unanswered tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

From the must unanswered tags is given: - number of questions and unanswered questions - number of questioners and respondents

Moderators

Q&A for community managers, administrators, and moderators

DECLARE @minQuestionCount int = 500
DECLARE @topN int = 50

SELECT TOP (@topN)
u.TagName, u.UnansweredCount, u.QuestionCount,
q.AskersCount,
a.AnswererCount,
CAST((100.0 * u.UnansweredCount / (1.0 * u.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
) u,
(
  -- All Questions grouped by tag
  SELECT 
    t.TagName AS TagName,
    COUNT(DISTINCT q.OwnerUserId) AS AskersCount
  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)
  GROUP BY TagName
) q,
(
  -- All Answers grouped by tag
  SELECT 
    t.TagName AS TagName,
    COUNT(DISTINCT a.OwnerUserId) AS AnswererCount
  FROM Posts a, Posts q
    INNER JOIN PostTags pt ON q.Id = pt.PostId
    INNER JOIN Tags t ON pt.TagId = t.Id
  WHERE 
    (a.PostTypeId = 2) AND
    (a.ParentId = q.Id)
  GROUP BY TagName
) a

WHERE 
  u.QuestionCount > @minQuestionCount AND
  q.TagName = u.TagName AND
  a.TagName = u.TagName
ORDER BY u.QuestionCount DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results