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; SELECT Q.MONTH, Q.Count AS "##Tag?firebase## Questions", Answers.Count AS "Answered", GooglerAnswers.Count AS "Googler Answers" FROM #questions 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 INNER JOIN #users ON UserId = A.OwnerUserId 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 ; 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) ;