This is a follow up to Top Active Answerers on a Stack Exchange site
Actual SEDE Query --> Top Active Answerers on site
Do I have a good understanding of the
RANK function? It seems pretty simple.
Should I separate this into 2 queries or is having 2 rank columns an acceptable thing? If it is acceptable have I displayed it properly? or does it look messy?
--@DaysSinceActivity is the amount of days that you would like to scan for activity --@NumberOfUsers is the number of users you want to return. SELECT TOP ##NumberOfUsers:int?1000## Users.Id AS [User Link], Users.DisplayName AS UserName, COUNT(Posts.Id) AS [Total Answers], RANK() OVER (ORDER BY (COUNT(Posts.Id)) DESC) AS [Total Answer Rank], CAST(AVG(CAST(Score AS float)) AS NUMERIC(6,2)) AS [Average Answer Score], RANK() OVER (ORDER BY (CAST(AVG(CAST(Score AS float)) AS NUMERIC(6,2))) DESC) AS [Avg Score Ranking] FROM Posts INNER JOIN Users ON Users.Id = OwnerUserId WHERE PostTypeId = 2 AND CommunityOwnedDate IS NULL AND ClosedDate IS NULL AND Users.LastAccessDate > DATEADD(DAY, -##DaysSinceActivity:int?31##, GETDATE()) GROUP BY Users.Id, Users.DisplayName HAVING COUNT(Posts.Id) > 10 ORDER BY [Average Answer Score] DESC
The Ranking on those two rows seems really redundant because they are calculated fields.
DisplayName in the
GROUP BY is because I didn't know where else to put that, when I removed it I got an error saying
Column 'Users.DisplayName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I will probably use it in the
ORDER BY statement, I think I can get away with that.
GROUP BYon any column that is not an aggregate, if you have aggregated columns. That's by design in all SQL. \$\endgroup\$
ORDER BY [Total Answers] DESC? \$\endgroup\$