7
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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. \$\endgroup\$ – Phrancis Sep 19 '14 at 18:34
  • \$\begingroup\$ Just personal opinion, but judging by the title of your query, wouldn't it make more sense to ORDER BY [Total Answers] DESC? \$\endgroup\$ – Phrancis Sep 19 '14 at 18:35
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Malachi Sep 19 '14 at 18:40
6
\$\begingroup\$

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,
            Users.DisplayName AS UserName,
            COUNT(Posts.Id) AS AnswerCount,
            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##
        UserId AS [User Link],
        UserName AS [UserName],
        AnswerCount AS [Total Answers],
        RANK() OVER (ORDER BY AnswerCount DESC) AS [Total Answer Rank],
        AvgScore AS [Average Answer Score],
        RANK() OVER (ORDER BY AvgScore DESC) AS [Avg Score Ranking]
    FROM
        RawData
    WHERE
        AnswerCount > 10
    ORDER BY
        AvgScore DESC
    

    Note how the complex aggregates happen in just one place.

| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ I found another query, after I posted this, that does something similar. I like this it makes sense. \$\endgroup\$ – Malachi Sep 19 '14 at 18:51
0
\$\begingroup\$

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

| improve this answer | |
\$\endgroup\$
  • 4
    \$\begingroup\$ 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). \$\endgroup\$ – rolfl Sep 19 '14 at 18:31
  • \$\begingroup\$ I was told it won't sort correctly on [User Link] in rolfl's answer \$\endgroup\$ – Malachi Sep 19 '14 at 18:32
  • \$\begingroup\$ @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. \$\endgroup\$ – 200_success Sep 19 '14 at 18:37
  • \$\begingroup\$ 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. \$\endgroup\$ – Malachi Sep 19 '14 at 18:44
  • 3
    \$\begingroup\$ 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. \$\endgroup\$ – rolfl Sep 19 '14 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.