11
\$\begingroup\$

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?

\$\endgroup\$
  • \$\begingroup\$ I can't think of anything I would change! \$\endgroup\$ – Phrancis Dec 12 '14 at 17:57
  • \$\begingroup\$ SQL has a comma-first syntax convention? \$\endgroup\$ – konijn Dec 12 '14 at 18:40
  • 3
    \$\begingroup\$ @konijn it's not uncommon. It makes it easier to not add an extra comma at the end of the list. I also find it easier to add stuff to the end of the line without mucking about with the trailing comma. \$\endgroup\$ – MikeTheLiar Dec 12 '14 at 20:37
  • \$\begingroup\$ Why do you select Users.Id as [User Link] and DisplayName? \$\endgroup\$ – Tim Stone Dec 12 '14 at 23:45
  • \$\begingroup\$ @TimStone it is exactly that in the SEDE query, a link to the user's profile on the site \$\endgroup\$ – Malachi Dec 14 '14 at 3:48
8
\$\begingroup\$

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;
| improve this answer | |
\$\endgroup\$
6
\$\begingroup\$

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!

| improve this answer | |
\$\endgroup\$
4
\$\begingroup\$

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
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ the reason I don't do it for the Group By and the Order By is because they only had one term or were not complex enough to warrant spreading them out. honestly the GROUP BY User.Id was an after thought when I added the user link column \$\endgroup\$ – Malachi Dec 12 '14 at 19:06
  • \$\begingroup\$ And that's pretty fair @Malachi. Like I said, it was really nit picky. \$\endgroup\$ – RubberDuck Dec 12 '14 at 19:08
  • \$\begingroup\$ it's good to keep me on my toes....lol I like to stay consistent so that when I read my code I remember what is going on accurately \$\endgroup\$ – Malachi Dec 12 '14 at 19:09

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.