0

Please login or register to vote for this query.

(click on this box to dismiss)

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
*/

fork query

edited dec 1 Jesse.Scherer

created jun 3 18 Frank van Puffelen