Top Active Answerers on a Stack Exchange site

Question

I created this query back in January to find the top active answerers on Code Review and thought that it might be a good idea to get other opinions on my SQL coding using a database that we can all access.

This will probably be the first of several queries that I post for review.

Actual SEDE Query --> Top Active Answerers on site

--@DaysSinceActivity is the amount of days that you would like to scan for activity
--@NumberOfUsers is the number of users you want to return.

DECLARE @DaysSinceActivity INT = ##DaysSinceActivity##


SELECT 
    TOP ##NumberOfUsers##
    Users.Id as [User Link],
    Count(Posts.Id) AS Answers,
    CAST(AVG(CAST(Score AS float)) as numeric(6,2)) AS [Average Answer Score]
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, -31, GetDate())
GROUP BY
    Users.Id, DisplayName
HAVING
    Count(Posts.Id) > 10
ORDER BY
    [Average Answer Score] DESC

Answer 1

Dumping some SEDE experience in as well as a SQL review.

SEDE Specific things first

Parameters

SEDE caches the results of a query if it is run with the current data set (caches are cleared when the data is refreshed). If you run the same query twice, the second time will return the cached data. If the query has not been run, then you need to be logged in to refresh the cache. If the query has been run, then you do not need to be logged in to SEDE to see the data. As a consequence, if you share links, you should pre-populate the cache too.

Your query is parametrized, and as a result, you do not have any cached results... BUT.... SEDE parameters are of the form ##Name:Type?Default##. Use this to your advantage. By supplying a default value your query will likely be 'prepopulated'

So, set up your parameters like:

• ##DaysSinceActivity:int?30##
• ##NumberOfUsers:int?1000##

That way, people clicking through will have the default values populated, and will possibly get a better experience.

Display Name

In SEDE you can sort the data based on the columns. The [User Link] column does not sort by the name though, it sorts by the URL for the user, which, in essence, does an alphabetic sort of the user-id because that is where the URL starts to change....

Add the Display Name in as a result column as well as the [User Link] so people can sort the data alphabetically.... (because people like to know where they are...).

Ranking

Consider adding a RANK column as well, so people an sort by other columns, and still see their ranking.

SQL

you have already seen inconsistencies in the capitalization of the keywords... You have chosen upper-case, which is fine, but then these should all be upper-case:

• and
• null
• Count
• is
• etc.

Bug

You supply the last-access date as a parameter, but then you ignore it:

AND Users.LastAccessDate > DateAdd(Day, -31, GetDate())

code it to 31 all the time.... hmmm.

Answer 2

GROUP BY Users.Id is sufficient. There is no reason why you also need to GROUP BY DisplayName.