# Tag badges per user SEDE query

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.DisplayName
FROM
INNER JOIN Tags ON Tags.TagName = Badges.Name
GROUP BY Users.DisplayName, Users.Id


What can be done to make this a better query?

• I can't think of anything I would change! – Phrancis Dec 12 '14 at 17:57
• SQL has a comma-first syntax convention? – konijn Dec 12 '14 at 18:40
• @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. – MikeTheLiar Dec 12 '14 at 20:37
• Why do you select Users.Id as [User Link] and DisplayName? – Tim Stone Dec 12 '14 at 23:45
• @TimStone it is exactly that in the SEDE query, a link to the user's profile on the site – Malachi Dec 14 '14 at 3:48

## 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
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
, RANK() OVER (PARTITION BY Users.Id, Badges.Name ORDER BY Date) AS Level
FROM Users
INNER JOIN Tags ON Badges.Name = Tags.TagName
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
GROUP BY UserId, DisplayName
)
SELECT RANK() OVER (ORDER BY Gold DESC, Silver DESC, Bronze DESC) AS Rank
, DisplayName
, Gold
, Silver
, Bronze
, Total
ORDER BY Gold DESC, Silver DESC, Bronze DESC;


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.DisplayName
FROM
JOIN Tags ON Tags.TagName = Badges.Name
GROUP BY Users.DisplayName, Users.Id


All this is nitpicking though, nice query!

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.DisplayName

• 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 – Malachi Dec 12 '14 at 19:06