Find Users by Most Badges

Question

I very infrequently use SQL. I need more experience, so I have created a data explorer query to find those users with most of any particular badge (have to edit the query itself right now to alter it). I like following best practices, so please feel free to nit-pick and really do this right (indentation, capitalization, everything).

I have commented code that I cannot get to work (I would like it to work by user input), I hope my intent is quite obvious.

--declare @BadgeName nvarchar(50) = ##BadgeName:string##
--declare @MyTop int = ##MyTop:int##
select
-- top @MyTop
 top 100
 Badges.UserId as [User Link],
 Users.Reputation,
 --Badges.Name,
 count(Badges.Id) as NumberOfBadges
 from 
 Badges 
 inner join 
 Users On Users.Id=Badges.UserId
 where 
-- Badges.Name = @BadgeName and
 Badges.Name = 'Great Answer' and
 Users.Reputation > 10000
group by 
 Badges.UserId, Users.Reputation
order by 
 NumberOfBadges desc

Here's the actual query if you want to run it:

http://data.stackexchange.com/stackoverflow/query/271133/search-users-by-number-of-badges

Answer 1

DECLARE @BadgeName AS NVARCHAR(50) = ##BadgeName:string?Great Answer##;
DECLARE @MyTop AS INT = ##MyTop:int?100##;
SELECT TOP(@MyTop)
 Badges.UserId AS [User Link],
 Users.Reputation,
 @BadgeName AS BadgeName,
 COUNT(Badges.Id) AS NumberOfBadges
FROM Badges 
 INNER JOIN Users ON Users.Id=Badges.UserId
WHERE Badges.Name = @BadgeName
 AND Users.Reputation > 10000
GROUP BY Badges.UserId, Users.Reputation
ORDER BY NumberOfBadges DESC

The DECLARE statements should have semi-colons at the end.

Apparently you need the parentheses when using SELECT TOP with a variable.

As you noticed, you need to put columns in the GROUP BY to include them in the SELECT. You can get around this by using the variable instead of the column.

I prefer capitalized keywords and a more compact formatting, but that's really up to you.

Answer 2

group by and order by are both subordinate clauses to select, and should therefore be indented at the same level as from and where.

• Aaron Hall: OK, anything else?

Answer 3

Nothing bad to say, really!

It's a pretty simple query so only so much can be said about it. Your keyword capitalization is consistent, your columns are well-referenced, overall nothing stands out. The only portion I feel is a bit odd is this:

and Users.Reputation > 10000

Not exactly sure why you would want to add that restriction, but that's completely subjective to my opinion.

Great job!