Answer volume for top Firebasers by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Academia Meta

Q&A about the site for academics and those enrolled in higher education

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')
*/
;

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


SELECT * FROM #answers
WHERE Name IN (
  SELECT TOP 10 Name FROM #answers WHERE MONTH IN (
    SELECT TOP 10 MONTH FROM #answers ORDER BY MONTH DESC
  ) ORDER BY Count DESC
)
ORDER BY MONTH DESC

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results