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

CREATE TABLE #users (UserId nvarchar (35), Name nvarchar (100));

INSERT INTO #users(UserId, Name) VALUES 
(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, 'paulbeusterien'),
(6253640, 'mcg'),
(4816918, 'jhuleatt'),
(7164592, 'personjennifer'),
(6122677, 'kianam'),
(2207583, 'tburner'),
(1973552, 'ulukaya'),
(10880930, 'sumit'),
(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')
;

CREATE TABLE #questions (MONTH Date, Count Int);

INSERT INTO #questions(MONTH, Count)
  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)
;
SELECT * from #questions ORDER BY MONTH;

CREATE TABLE #answers (MONTH Date, Count Int);
INSERT INTO #answers(MONTH, Count)
  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 = 2 AND 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, CreationDate), 0)

SELECT * from #answers ORDER BY MONTH;


/*
SELECT Q.MONTH, Q.Count AS "##Tag?firebase## Questions", 
(Q.Count - Closed.Count) AS "Unclosed 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 EXISTS(SELECT * FROM #users WHERE UserId = A.OwnerUserId)
  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
LEFT JOIN (
  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' AND ClosedDate > '2015-01-01'
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
) AS Closed
ON Q.MONTH = Closed.MONTH
ORDER BY Q.Month
;

--- Test queries below

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS Month, COUNT(*)
FROM Posts
WHERE PostTypeId = 2
  AND EXISTS(SELECT * FROM #users WHERE OwnerUserId = UserId)
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
;

  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' AND ClosedDate > '2015-01-01'
  GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0)
*/

view current version

edited may 31 20

created jul 12 16 Frank van Puffelen