Questions answered last month by reputation

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 Q.Id, Sum(IIF(U.Reputation BETWEEN 0 AND 500, 1, 0)) As Rep500, Sum(IIF(U.Reputation BETWEEN 501 AND 1000,1, 0)) AS Rep1000, Sum(IIF(U.Reputation BETWEEN 1001 AND 3000,1, 0)) AS Rep3000, Sum(IIF(U.Reputation BETWEEN 3001 AND 10000,1, 0)) AS Rep10000, Sum(IIF(U.Reputation BETWEEN 10001 AND 20000,1, 0)) AS Rep20000, Sum(IIF(U.Reputation > 20000,1, 0)) AS RepMax
INTO #RepAnswers
FROM Posts AS Q LEFT JOIN (Posts AS A INNER JOIN Users AS U ON A.OwnerUserId=U.Id) ON A.ParentId=Q.Id
WHERE Q.ParentID Is Null AND Q.CreationDate >  DateAdd(m, -1, GETDATE())
GROUP BY Q.Id

GO

SELECT 1 AS No, 'Questions answers 20001+ Rep' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE RepMax>0
UNION SELECT 2 AS No, 'Questions answers 10001 - 20000 Rep' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE Rep20000>0
UNION SELECT 3 AS No, 'Questions answers 3001 - 10000 Rep' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE Rep10000>0
UNION SELECT 4 AS No, 'Questions answers 1001 - 3000 Rep' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE Rep3000>0
UNION SELECT 5 AS No, 'Questions answers 501 - 1000 Rep' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE Rep1000>0
UNION SELECT 6 AS No, 'Questions answers < 500 Rep' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE Rep500>0
UNION SELECT 7 AS No, 'Questions with no answers' AS [Column], Count(Id) AS Number
FROM #RepAnswers WHERE Rep500=0 AND Rep1000=0 AND Rep3000=0 AND Rep10000=0 AND Rep20000=0 AND RepMax=0
UNION SELECT 8 AS No, 'Questions total' AS [Column], Count(Id) AS Number
FROM #RepAnswers

Enter Parameters

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