5
\$\begingroup\$

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

\$\endgroup\$
6
\$\begingroup\$
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.

\$\endgroup\$
5
\$\begingroup\$

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

\$\endgroup\$
  • \$\begingroup\$ OK, anything else? \$\endgroup\$ – Aaron Hall Feb 6 '15 at 23:31
3
\$\begingroup\$

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!

\$\endgroup\$

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.