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

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

INSERT INTO #answers

SELECT 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0) AS [MONTH]
  , Name AS UserId
  , COUNT(*) AS Count
FROM Posts
JOIN #users ON Posts.OwnerUserId = #users.UserId
WHERE OwnerUserId IN (SELECT UserId FROM #users)
  AND CreationDate > '2017-01-01'
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CreationDate), 0), Name
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