WITH userPosts AS (SELECT SUM(CASE WHEN pt.Name='Question...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH userPosts AS (SELECT
  SUM(CASE WHEN pt.Name='Question' THEN 1 ELSE 0 END) As Questions,
  SUM(CASE WHEN pt.Name='Answer' THEN 1 ELSE 0 END) As Answers,
  YEAR(u.CreationDate) As Year
FROM Posts p
JOIN PostTypes pt ON pt.id = p.PostTypeId
JOIN Users u on p.OwnerUserId = u.Id
WHERE DATEDIFF(Year, u.CreationDate, p.CreationDate) < 1
GROUP BY OwnerUserId, u.CreationDate)
SELECT Year, 
SUM(CASE WHEN Questions > 0 AND Answers = 0 THEN 1 ELSE 0 END) AS Askers,
SUM(CASE WHEN Questions = 0 AND Answers > 0 THEN 1 ELSE 0 END) AS Answers,
SUM(CASE WHEN Questions > 0 AND Answers > 0 THEN 1 ELSE 0 END) AS Both
FROM userPosts
GROUP BY Year
ORDER BY Year

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results