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.

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!