Number of answers posted by Top X users last month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Server Fault

Q&A for system and network administrators

SET NOCOUNT ON

SELECT U.Id AS UserId, Count(A.Id) AS Answers
INTO #Answers
FROM Posts AS A INNER JOIN Users AS U ON A.OwnerUserId=U.Id
WHERE A.ParentID Is Not Null AND A.CreationDate >  DateAdd(m, -1, GETDATE())
GROUP BY U.Id
ORDER BY Count(A.Id) DESC

GO

DECLARE @TotalAnswers int;

SELECT @TotalAnswers=Sum(Answers) FROM #Answers;

SELECT 1 AS No, 'Top 5 users' AS [Column], Sum(A.Answers) AS Answers, CAST(Sum(A.Answers) AS smallmoney) / @TotalAnswers * 100 AS Percentage FROM #Answers AS A INNER JOIN (SELECT TOP 5 * FROM #Answers ORDER BY Answers DESC) AS L ON A.UserId = L.UserId
UNION SELECT 2 AS No, 'Top 10 users' AS [Column], Sum(A.Answers) AS Answers, CAST(Sum(A.Answers) AS smallmoney) / @TotalAnswers * 100 AS Percentage FROM #Answers AS A INNER JOIN (SELECT TOP 10 * FROM #Answers ORDER BY Answers DESC) AS L ON A.UserId = L.UserId
UNION SELECT 3 AS No, 'Top 50 users' AS [Column], Sum(A.Answers) AS Answers, CAST(Sum(A.Answers) AS smallmoney) / @TotalAnswers * 100 AS Percentage FROM #Answers AS A INNER JOIN (SELECT TOP 50 * FROM #Answers ORDER BY Answers DESC) AS L ON A.UserId = L.UserId
UNION SELECT 4 AS No, 'Top 100 users' AS [Column], Sum(A.Answers) AS Answers, CAST(Sum(A.Answers) AS smallmoney) / @TotalAnswers * 100 AS Percentage FROM #Answers AS A INNER JOIN (SELECT TOP 100 * FROM #Answers ORDER BY Answers DESC) AS L ON A.UserId = L.UserId
UNION SELECT 5 AS No, 'Top 500 users' AS [Column], Sum(A.Answers) AS Answers, CAST(Sum(A.Answers) AS smallmoney) / @TotalAnswers * 100 AS Percentage FROM #Answers AS A INNER JOIN (SELECT TOP 500 * FROM #Answers ORDER BY Answers DESC) AS L ON A.UserId = L.UserId
UNION SELECT 6 AS No, 'Total answers last month' AS [Column], @TotalAnswers, 100 AS Percentage

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results