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##
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


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.

• It's actually normal that you have to GROUP BY on any column that is not an aggregate, if you have aggregated columns. That's by design in all SQL. – Phrancis Sep 19 '14 at 18:34
• Just personal opinion, but judging by the title of your query, wouldn't it make more sense to ORDER BY [Total Answers] DESC? – Phrancis Sep 19 '14 at 18:35
• what makes them top answerers is the quality of their answers right? I agree that I need to either change the title or define what being a Top Answerer means. – Malachi Sep 19 '14 at 18:40

This is a substantial improvement over the previous version. The code is consistent, I do not see any bugs, it's generally good.

There are two further opportunities for improvement that I can see.

1. I can tell you do this to get an accurate average, and round it to 2 decimal places. The concept is good:

CAST(AVG(CAST(Score AS float)) AS NUMERIC(6,2))


but, there's a trick with float that simplifies things a bit. Consider:

CAST(AVG(Score * 1.0) AS NUMERIC(6,2))


I know it is small, but the implicit cast makes things easier to read.

2. You duplicate calculations once for the base value, then again for the rank, and again in the having clause. These complex aggregates can be simplified by using a CTE. Calculate the raw data in one CTE and do the calculations in that, then, use that raw data as part of the outer select. Describing it is harder than just doing it. Consider the following query which produces the same results as yours:

WITH RawData AS (

SELECT
Users.Id AS UserId,
CAST(AVG(1.0 * Score) AS NUMERIC(6,2)) AS AvgScore
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

)
SELECT
TOP ##NumberOfUsers:int?1000##
RANK() OVER (ORDER BY AvgScore DESC) AS [Avg Score Ranking]
FROM
RawData
WHERE
ORDER BY
AvgScore DESC


Note how the complex aggregates happen in just one place.

• I found another query, after I posted this, that does something similar. I like this it makes sense. – Malachi Sep 19 '14 at 18:51

Selecting Users.DisplayName to be displayed seems redundant. When [User Link] will automatically render the user using the display name anyway.

• That was based on my recommendation specific for SEDE, so you can sort by user-name in the output. Otherwise it is not possible (sorting the link column does not do a good sort). – rolfl Sep 19 '14 at 18:31
• I was told it won't sort correctly on [User Link] in rolfl's answer – Malachi Sep 19 '14 at 18:32
• @rolfl Is the display name an attribute to meaningfully sort by? If you're looking for a particular username, ⌘F or Ctrl-F would work. – 200_success Sep 19 '14 at 18:37
• In SSMS you don't have that functionality like you do on a website. But here it is displayed on a website. I know how to find my name real fast if it is sorted alphabetically. Your Point is valid and depends on the user. other than that I don't think that it hurts performance. – Malachi Sep 19 '14 at 18:44
• Ctrl-F will work, sure, but it is not intuitive. I guess having two columns is not intuitive either. Personally I find it nice to sort by name, everyone wants to see where they, and their buddies are. Ctrl-F for each of them is... hard. – rolfl Sep 19 '14 at 18:51