Q&A about the site for people interested in conceptual questions about life and challenges in a world where "cognitive" functions can be mimicked in purely digital environment
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'), (1, 'Google Assistant'), (12234169, 'manchan'), (1491707, 'felker'), (5733219, 'atulep'), (2756266, 'taycaldwell'), (12221640, 'jearleycha'), (2, 'Smart Home'), (656408, 'proppy'), (10631310, 'tklopfenstein'), (3, 'App actions'), (1245915, 'diegozuluaga'), (4, 'Other'), (246461, 'smithdave'), (59534213, 'martinpatrick'), (8200631, 'kevin-kokomani'), (8697185, 'scott-crossen'), (3618671, 'misha-brukman'), (281221, 'peter-friese'), (4092401, 'ying-li'), (6122677, 'kianam'), (7644371, 'jim-morrison'), (14466144, 'brettsneed'), (13451151, 'kelsk'), (5, '20211014'), (8935548, 'mgoya'), (12857703, 'rafael-lemos'), (365738, 'sethvargo'), (1476372, 'tony-j-huang'), (5385206, 'steve-ganem'), (16167525, 'vsfan'), (12919863, 'maksym-malyhin'), (1290844, 'vkryachko'), (12015022, 'wesley-tarle'), (13925433, 'alex-volkovitsky') ; SELECT Q.MONTH, Q.Count AS "##Tag?firebase%## questions", (Q.Count - Closed.Count) AS "Unclosed questions", Answers.Count AS "Questions with answer", GooglerAnswers.Count AS "Questions with Googler answer" 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 LIKE '##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 LIKE '##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 LIKE '##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 LIKE '##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 ;