Q&A for passionate videogamers on all platforms
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'), (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'), (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'), /* Jesse's minions */ (5519300, 'tarik-huber'), (2301161, 'digi-byte'), (13130697, 'dharmaraj'), (10136013, 'peter-o'), (11039164, 'victor-eronmosele') /* -- (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') */ ; CREATE TABLE #answers (MONTH date, Name nvarchar(100), Count int); INSERT INTO #answers SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [MONTH] , Name , COUNT(*) AS Count FROM Posts INNER JOIN #users ON Posts.OwnerUserId = #users.UserId WHERE CreationDate > '2017-01-01' -- AND CreationDate < '2020-06-01' GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), Name ORDER BY [MONTH] DESC CREATE TABLE #totals (Name nvarchar(100), Count int); INSERT INTO #totals SELECT Name, SUM(Count) AS Count FROM #answers GROUP BY Name CREATE TABLE #topusers (Name nvarchar(100)); INSERT INTO #topusers SELECT Name FROM ( SELECT TOP 10 Name FROM #answers ORDER BY Count DESC) first UNION SELECT Name FROM ( SELECT TOP 10 Name FROM #totals ORDER BY Count DESC) second UNION SELECT Name FROM ( SELECT TOP 10 Name FROM #answers WHERE [MONTH] = (SELECT MAX(MONTH) FROM #answers)) third SELECT * FROM #answers WHERE Name IN (SELECT Name FROM #topusers) UNION SELECT [MONTH], '(Others)' AS Name, SUM(Count) AS Count FROM #answers WHERE Name NOT IN (SELECT Name FROM #topusers) GROUP BY [MONTH] /* SELECT * FROM #answers WHERE Name IN ( SELECT TOP 20 Name FROM #answers WHERE MONTH IN ( SELECT TOP 20 MONTH FROM #answers ORDER BY MONTH DESC ) ORDER BY Count DESC ) ORDER BY MONTH DESC */