27
\$\begingroup\$

Description

Calculate how many mugs StackExchange should give away(for graduation) to top reputed users on codereview.stackexchange.com so I'll also receive one. (output is divisible by 50).

Code

DECLARE @mugs INT,@rounded_mugs INT,@inc INT,@remainder INT;
SELECT  @mugs = COUNT(1) FROM Users WHERE Reputation >= 
  (SELECT Reputation FROM Users WHERE DisplayName='JaDogg');
SET  @remainder = @mugs%50;
IF (@remainder = 0) SET @inc = 0 ELSE SET @inc=50-@remainder; 
SELECT  @mugs+@inc AS 'AMOUNT OF MUGS TO GIVE AWAY';

Link

Calculate amount of mugs to give away so I'll receive one

\$\endgroup\$
15
\$\begingroup\$

Documentation

I think that what the query is doing is looking at your reputation rank, then rounding up to the nearest 50. It would be nice to hit "edit description" and document that for the benefit of anyone else using Stack Exchange Data Explorer.

Generality

Data Explorer allows parameterization. Use it.

Style

You shouldn't have to declare variables for such a simple query. In SQL, try to say what you want. Don't state the steps for calculating it.

A good way to avoid defining variables is to use Common Table Expressions (the WITH clause) instead.

Brevity

It would help to use the relevant functions RANK() and CEILING().

WITH UserRanks AS (
    SELECT DisplayName
         , RANK() OVER (ORDER BY Reputation DESC) AS RepRank
        FROM Users
)
SELECT *, 50 * CEILING((RepRank + 0.0) / 50) AS BatchedRepRank
    FROM UserRanks
    WHERE DisplayName = ##Username:string##;
\$\endgroup\$
  • 1
    \$\begingroup\$ Since RepRank is an integer, you can also do 50*((RepRank + 49)/50) to avoid type conversions. \$\endgroup\$ – fluffy Oct 24 '14 at 5:16
  • \$\begingroup\$ Silly me. I only needed one CTE, not two. Corrected in Rev 2. \$\endgroup\$ – 200_success Oct 24 '14 at 8:34
10
\$\begingroup\$

Stylistically I'd declare variables just like in program code: Each on a single line.

Also your nested Select seems a little forced and cramped, as well as the calculation code. You maybe want to add a little whitespace there ;)

Well now now, I actually wouldn't want you to get a Mug, but I'd want one myself...
slightly jesting

How many mugs would have to be given away, for me to recieve one? Your query cannot answer that. As already pointed out by Mat'sMug (badum, tss) in a comment, you could use a username parameter.

I would have written this somewhat like:

DECLARE @mugs INT;
DECLARE @rounded_mugs INT;
DECLARE @inc INT;
DECLARE @remainder INT;

SELECT  @mugs = COUNT(1) 
FROM Users 
WHERE Reputation >= (SELECT Reputation FROM Users WHERE DisplayName=##username:string?Community##);

SET  @remainder = @mugs%50;
IF (@remainder = 0) 
  SET @inc = 0 
ELSE 
  SET @inc=50-@remainder; 

SELECT  @mugs+@inc AS 'Amount of mugs to give away';
\$\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.