Tweeted twitter.com/#!/StackCodeReview/status/625083502233579520

occurred Jul 25, 2015 at 23:21

edited tags

Link

edited Jul 24, 2015 at 6:09

Jamal

35.2k
13
134
238

fixed syntax highlighting

Source Link

edited Jul 24, 2015 at 1:25

Mathieu Guindon

75.6k
18
195
469

WITH FirstAnswers AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 2 --answer AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ), FirstQuestions AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 1 --question AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ) SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf , a.AnswerCount, b.QuestionCount FROM ( SELECT WeekOf, Count(UserId) AnswerCount FROM FirstAnswers GROUP BY WeekOf ) a FULL OUTER JOIN ( SELECT WeekOf, Count(UserId) QuestionCount FROM FirstQuestions GROUP BY WeekOf )b ON a.WeekOf = b.WeekOf ORDER BY WeekOf

WITH FirstAnswers AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 2 --answer AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ), FirstQuestions AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 1 --question AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ) SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf , a.AnswerCount, b.QuestionCount FROM ( SELECT WeekOf, Count(UserId) AnswerCount FROM FirstAnswers GROUP BY WeekOf ) a FULL OUTER JOIN ( SELECT WeekOf, Count(UserId) QuestionCount FROM FirstQuestions GROUP BY WeekOf )b ON a.WeekOf = b.WeekOf ORDER BY WeekOf

WITH FirstAnswers AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 2 --answer AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ), FirstQuestions AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 1 --question AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ) SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf , a.AnswerCount, b.QuestionCount FROM ( SELECT WeekOf, Count(UserId) AnswerCount FROM FirstAnswers GROUP BY WeekOf ) a FULL OUTER JOIN ( SELECT WeekOf, Count(UserId) QuestionCount FROM FirstQuestions GROUP BY WeekOf )b ON a.WeekOf = b.WeekOf ORDER BY WeekOf

WITH FirstAnswers AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 2 --answer AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ), FirstQuestions AS ( SELECT Users.Id UserId ,dateadd(week, datediff(week, 0, Convert(Date,Min(Posts.CreationDate))), 0) WeekOf FROM Posts INNER JOIN Users ON Posts.OwnerUserId = Users.Id WHERE PostTypeId = 1 --question AND Posts.CreationDate > '2011-Jan-01' --There was very little activity prior to this date. Including it skews the graph. GROUP BY Users.Id ) SELECT ISNULL(a.WeekOf,b.WeekOf) As WeekOf , a.AnswerCount, b.QuestionCount FROM ( SELECT WeekOf, Count(UserId) AnswerCount FROM FirstAnswers GROUP BY WeekOf ) a FULL OUTER JOIN ( SELECT WeekOf, Count(UserId) QuestionCount FROM FirstQuestions GROUP BY WeekOf )b ON a.WeekOf = b.WeekOf ORDER BY WeekOf