2

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

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##'
    WHERE 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
    0
  )
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, A.CreationDate), 0)
  GO

SELECT Q.Week, Q.Count AS Questions, A.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 = A.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
    0
  )
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, A.CreationDate), 0)
) AS A
ON Q.Week = A.Week

view current version

edited jul 13 16

created jul 12 16 Frank van Puffelen