Answer volume for top Firebasers by month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Board and Card Games

Q&A for people who like playing board games, designing board games or modifying the rules of existing board games

CREATE TABLE #users (UserId nvarchar (35), Name nvarchar (35));

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, 'paul-beusterien'),
(6253640, 'gil-gilbert'),
(4816918, 'jhuleatt'),
(7164592, 'personjennifer'),
(6122677, 'kianam'),
(2207583, 'tburner');

CREATE TABLE #answers (MONTH date, UserId nvarchar (35), Count int);

INSERT INTO #answers

SELECT 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [MONTH]
  , OwnerUserId AS UserId
  , COUNT(*) AS Count
FROM Posts
WHERE OwnerUserId IN (SELECT UserId FROM #users)
/*WHERE OwnerUserId IN (
    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, -- paul-beusterien
    6253640, -- gil-gilbert
    4816918, -- jhuleatt
    7164592, -- personjennifer
    6122677, -- kianam
    2207583, -- tburner
    0
  )*/
  AND CreationDate > '2017-01-01'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), OwnerUserId
ORDER BY [MONTH] DESC


SELECT * FROM #answers
WHERE UserId IN (
  SELECT TOP 5 UserId FROM #answers WHERE MONTH IN (
    SELECT TOP 5 MONTH FROM #answers ORDER BY MONTH DESC
  ) ORDER BY Count DESC
)
ORDER BY MONTH DESC

Enter Parameters

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