0

Please login or register to vote for this query.

(click on this box to dismiss)

Finds the top X unanswered tags ("top" by number of questions), and then counts the number of answers given by each tag respondent.

Q&A about the site for enthusiasts and power users of Windows Phone OS

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

-- For top n most populous tags
SELECT
uqt.TagName,
ust.UserId, ust.AnswersCount
FROM
(
  -- All unanswered questions grouped by tag
  SELECT  TOP (@topN)
    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
      ORDER BY QuestionCount DESC
) uqt,
(
  -- All users grouped by tag
  SELECT 
    t.TagName AS TagName,
    u.Id AS UserId,
    COUNT(u.Id) AS AnswersCount
  FROM Tags t  
    JOIN PostTags pt ON pt.TagId = t.Id
    JOIN Posts p ON p.Id = pt.PostId 
    JOIN Posts as Answers ON Answers.ParentId = p.Id
    JOIN Users u ON u.Id = Answers.OwnerUserId
  GROUP BY t.TagName, u.Id
) ust

WHERE 
  uqt.QuestionCount > @minQuestionCount AND
  ust.TagName = uqt.TagName
ORDER BY 
  uqt.QuestionCount DESC,
  ust.AnswersCount DESC

fork query

edited may 3 12

created may 3 12 Nigini Abilio