0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for linguists, teachers and learners wanting to discuss the finer points of the Portuguese language

SELECT Q.WEEK, Q.Count AS "##Tag?firebase## Questions", 
  Answers.Count AS "Answered", GooglerAnswers.Count AS "Googler Answers"
FROM (
  SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) AS [WEEK]
    , 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 > '2016-01-01'
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)
) AS Q
LEFT JOIN (
  SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, A.CreationDate), 0) AS [WEEK]
    , 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 > '2016-01-01'
    AND A.OwnerUserId in (
    209103, -- puf
    394010, -- katowulf
    110915, -- mikelehen
    879295, -- rdimarco
    1373162, -- deast
    5385206, -- sganem
    3875023, -- mpmcdonald
    5081766, -- bojeil
    807126, -- dougstevenson
    1410370, -- arthurthompson
    839465, -- mimming
    324977, -- samstern
    819314, -- ianbarber
    1172232, -- gregsoltis
    937583, -- raynor
    613115, -- dgiorgino
    1915327, -- dmabin
    4186942, -- bjiang
    4625829, -- aalawi
    4447017, -- andysoto
    978525, -- eobrain
    9849, -- rsattar 
    2955366, -- jwenger
    1028647, -- jamesdaniels
    226391, -- bleigh
    7688078, -- rjh
    153407, -- danmcg
    3975963, -- mbonnell
    42960644, -- krausefx
    5190886, -- hkj
    2546685, -- oleksiyi
    556617, -- paul-beusterien
    6253640, -- gil-gilbert
    4816918, -- jhuleatt
    7164592, -- personjennifer
    6122677, -- kianam
    2207583, -- tburner
    1419434, -- Will Hayworth
    1973552, -- ulukaya
    10880930, -- Sumit Chandel
    11209105, -- Jeff Avis
    11229774, -- marcjordan
    5827947, -- isachen
    9789871, -- psanketi
    3945408, -- juanlara
    3043663, -- martinpatrick
    9754679, -- rich-gowman
    7644371, -- jim-morrison
    4092401, -- ying-li
    6122677, -- kianam
    11850212, -- ryanpbrewster
    0
  )
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, A.CreationDate), 0)
) AS GooglerAnswers
ON Q.WEEK = GooglerAnswers.WEEK
LEFT JOIN (
  SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, Q.CreationDate), 0) AS [WEEK]
    , 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 > '2016-01-01'
    AND EXISTS(SELECT Answers.Id FROM Posts AS Answers WHERE Answers.ParentId = Q.Id)
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, Q.CreationDate), 0)
) AS Answers
ON Q.WEEK = Answers.WEEK

fork query

edited dec 12 19

created jan 23 17 Frank van Puffelen