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.
The 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 BY
on 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\$