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 Q.Week, Q.Count AS Questions, A.Count AS "Googler Answers", P.Count AS "puf 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 > '2015-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
LEFT JOIN (
SELECT 
    DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0) AS [Week]
    , COUNT(*) AS Count
  FROM Posts
  WHERE CreationDate > '2015-01-01'
    AND OwnerUserId = 209103
  GROUP BY DATEADD(WEEK, DATEDIFF(WEEK, 0, CreationDate), 0)
) AS P
ON Q.Week = P.Week

view current version

edited jul 13 16

created jul 12 16 Frank van Puffelen