Android - Questions and answers by a group of users over time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Chess

Q&A for serious players and enthusiasts of chess

SELECT Q.MONTH, Q.Count AS "##Tag?firebase## Questions", 
  Answers.Count AS "Answered", GooglerAnswers.Count AS "Googler Answers"
FROM (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts
  INNER JOIN PostTags ON PostTags.PostId = Posts.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName = '##Tag##' AND PostTypeId = 1 AND CreationDate > '2015-01-01'
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
) AS Q
LEFT JOIN (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, A.CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts AS A
  INNER JOIN Posts AS Q ON A.ParentId = Q.Id
  INNER JOIN PostTags ON PostTags.PostId = Q.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName = '##Tag##'
    AND A.CreationDate > '2015-01-01'
    AND A.OwnerUserId in (
    497605, -- lyla
    304533, -- rahul
    1676363, -- ian
    118862, -- pietro
    9842357, -- sumir
    0
  )
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, A.CreationDate), 0)
) AS GooglerAnswers
ON Q.MONTH = GooglerAnswers.MONTH
LEFT JOIN (
  SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, Q.CreationDate), 0) AS [MONTH]
    , COUNT(*) AS Count
  FROM Posts AS Q
  INNER JOIN PostTags ON PostTags.PostId = Q.Id
  INNER JOIN Tags ON PostTags.TagId = Tags.Id
  WHERE Tags.TagName = '##Tag##'
    AND Q.CreationDate > '2015-01-01'
    AND EXISTS(SELECT Answers.Id FROM Posts AS Answers WHERE Answers.ParentId = Q.Id)
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, Q.CreationDate), 0)
) AS Answers
ON Q.MONTH = Answers.MONTH

Enter Parameters

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