Tag badges per user SEDE query

Question

I wanted to see where I lined up with other users on tag badges (I wanted to count just bronze but figured I would start with all of them to start out with).

Here is the query that I ended up with:

SELECT
    Users.Id as [User Link]
    , Users.DisplayName
    , COUNT (Badges.Id) AS Number_of_Tag_Badges
FROM 
    Badges
    INNER JOIN Tags ON Tags.TagName = Badges.Name
    INNER JOIN Users ON Users.Id = Badges.UserId
GROUP BY Users.DisplayName, Users.Id
ORDER BY Number_of_Tag_Badges DESC

What can be done to make this a better query?

Answer 1

Implementation

The query is quite well written for what it does. I just have a few minor nitpicks about its implementation.

The FROM, GROUP BY, and ORDER BY clauses are all subsidiary to the SELECT clause. Therefore, I would indent them by one level. (Indenting your INNER JOIN clauses so that they are part of the FROM clause was good.)

You're getting the disadvantages of the comma-first convention without reaping the benefits. The advantage of putting commas first is that it makes it foolproof to add or remove columns in the SELECT: you should be able to delete an entire line of text without having to worry about fixing commas on neighbouring lines. However, to take advantage of that property, you should put the first column on the same line as the SELECT keyword:

SELECT Users.Id as [User Link]
     , Users.DisplayName
     , COUNT (Badges.Id) AS Number_of_Tag_Badges
    FROM …

Remove the space before the parenthesis in the COUNT (…) call.

The joins would be better if the tables were listed linearly:

$$ \fbox{Users} \overset{\texttt{UserId}}{\longleftrightarrow} \fbox{Badges} \overset{\texttt{Name}}{\longleftrightarrow} \fbox{Tags}$$

Since the displayed results are user-centric, I would suggest listing the tables as Users, Badges, Tags rather than any other order.

Concept

It would be handy if you added a rank column.

The query doesn't distinguish between gold, silver, and bronze badges. You should be able to report the breakdown. The convention used by the International Olympic Committee is to sort by the gold medal count first, rather than by the total. Perhaps you should do the same.

WITH EarnedTagBadges AS (
    SELECT Users.DisplayName
         , Badges.UserId
         , Badges.Name
         , RANK() OVER (PARTITION BY Users.Id, Badges.Name ORDER BY Date) AS Level
        FROM Users
            INNER JOIN Badges ON Users.Id = Badges.UserId
            INNER JOIN Tags ON Badges.Name = Tags.TagName
), UserTagBadges AS (
    SELECT UserId
         , DisplayName
         , COUNT(CASE WHEN Level = 3 THEN 1 END) AS Gold
         , COUNT(CASE WHEN Level = 2 THEN 1 END) AS Silver
         , COUNT(CASE WHEN Level = 1 THEN 1 END) AS Bronze
         , COUNT(Level) AS Total
        FROM EarnedTagBadges
    GROUP BY UserId, DisplayName
)
SELECT RANK() OVER (ORDER BY Gold DESC, Silver DESC, Bronze DESC) AS Rank
     , UserId AS [User Link]
     , DisplayName
     , Gold
     , Silver
     , Bronze
     , Total
    FROM UserTagBadges
    ORDER BY Gold DESC, Silver DESC, Bronze DESC;

Answer 2

I don't like anything unnecessary in code. The "INNER" terms are unnecessary in this query. Some might say that INNER JOIN can be a bit clearer to read, especially if the query has other join types, but since in this question there's only one join type it's a moot point. In short, I'd drop the "INNER".

Another unnecessary element is the COUNT(Badges.Id), which could be simply COUNT(*) so I don't wonder about the possibility of differences.

When I compared the two versions of the query with COUNT(Badges.Id) and COUNT(*) I got a different ordering. Since we order by count, the ordering of lines with the same count is undefined. I'd recommend to add one more column for ordering to get more consistent output, for example:

SELECT
    Users.Id as [User Link]
    , Users.DisplayName
    , COUNT (*) AS Number_of_Tag_Badges
FROM 
    Badges
    JOIN Tags ON Tags.TagName = Badges.Name
    JOIN Users ON Users.Id = Badges.UserId
GROUP BY Users.DisplayName, Users.Id
ORDER BY Number_of_Tag_Badges DESC, Users.DisplayName

All this is nitpicking though, nice query!

Answer 3

Minor note on consistency. You use a newline for your SELECT and FROM, but not your ORDER BY. Otherwise, it's pretty straight forward. I don't see anything smelly about it.

SELECT
    Users.Id as [User Link]
    , Users.DisplayName
    , COUNT (Badges.Id) AS Number_of_Tag_Badges
FROM 
    Badges
    INNER JOIN Tags ON Tags.TagName = Badges.Name
    INNER JOIN Users ON Users.Id = Badges.UserId
GROUP BY 
    Users.DisplayName, Users.Id
ORDER BY 
    Number_of_Tag_Badges DESC