Users contribution by chosen tags

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT
uqt.TagName,
ust.UserId, ust.AnswersCount
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
) 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 
  ust.TagName = uqt.TagName AND
  uqt.TagName IN ('windows-7','windows-xp','linux','ubuntu','osx','mac','unix','solaris','freebsd')

Enter Parameters

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