3
\$\begingroup\$

I put together this simple query to calculate the percentage of downvotes each user is responsible for and displaying them in order of downvote percentage. It's generating the desired results, but I am uncertain about style and best practices.

WITH DownVoteCount AS (
SELECT DownVoteCount = CAST(SUM(DownVotes) AS float)
FROM Users
), DownVoteWeight AS (
SELECT DisplayName, UpVotes, DownVoteWeight = ROUND(DownVotes / (SELECT DownVoteCount from DownVoteCount) * 100, 4)
FROM Users
Where Users.DownVotes > 0
)

SELECT DisplayName, DownVoteWeight
FROM DownVoteWeight

ORDER BY DownVoteWeight DESC, UpVotes

I read about the capitalization of keywords, and decided it would be useful in case of working with SQL inside strings, where syntax highlighting wouldn't be available.

\$\endgroup\$
4
\$\begingroup\$

SQL casing shouldn't matter (much)

Every SQL developer I know writes their queries a bit differently. Case/style shouldn't matter much or at all, if the SQL code works but it's a bit sloppy. Favor substance over style. For example, these 3 queries are identical, as far as the database engine cares:

/* These are all exactly the same in SQL: */
SELECT FOO, BAR FROM TEST;
Select Foo, Bar From Test;
select foo, bar from test;

That being said...

...there are a few things we could probably improve in your own query...

Some indentation would feel nice, especially in the subqueries/CTEs. Some indentation will make your code much easier to read:

WITH foo AS (
    ... 
),
bar AS (
    ... 
)
--etc

Lines breaks

Line breaks...also feel nice, especially for breaking down business logic, which DownVoteWeight is (for instance), where it can be broken down into meaningful functions/operations. This one might deserve a bit more documentation.

SELECT 
    DisplayName, 
    UpVotes, 
    DownVoteWeight = ROUND(
        DownVotes / (SELECT DownVoteCount from DownVoteCount) * 100, 4
    )
|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ Nice. I didn't do any indentation since SEDE automatically trims it when you write certain keywords/characters. The linebroken business logic looks a lot better, thanks! \$\endgroup\$ – Amani Kilumanga Jan 30 '16 at 4:49

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.