0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

SELECT
  YEAR(q.CreationDate) AS Year,
  COUNT(DISTINCT CASE WHEN a.AcceptedAnswerId IS NULL THEN q.Id END) AS UnacceptedAnswers,
  AVG(AnswerCounts.AnswerCount) AS AvgAnswers
FROM
  Posts q
  INNER JOIN (
    SELECT ParentId, COUNT(*) AS AnswerCount
    FROM Posts
    WHERE PostTypeId = 2
    GROUP BY ParentId
  ) AS AnswerCounts ON q.Id = AnswerCounts.ParentId
  LEFT JOIN Posts a ON a.Id = q.AcceptedAnswerId
WHERE
  q.PostTypeId = 1 AND
  YEAR(q.CreationDate) >= 2008
GROUP BY
  YEAR(q.CreationDate)
ORDER BY
  YEAR(q.CreationDate)

fork query

edited apr 23 23

created apr 23 23 jon.doe32071