Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

About

This is a SEDE query which will calculate your activity and participation on a site. It's geared for Programming Puzzles & Code Golf where questions are values more, but the values can be modified to fit general Q&A SE sites too.

Try it online!


So I did what all programmers do and I looked up how to most of this stuff on Stack Overflow and mashed the snippets from SO into my program until it worked!


Essentially this program takes input for the user's User ID and their score is calculated by::

1  * <# of Upvotes>
2  * <# of Downvotes>
10 * <# of Questions>
5  * <# of answers>
1  * <# of Comments>

The actual values of what <# of ...> is multiplied is stored in variables which you can see I've declared.


Then within the FROM the Upvotes, Downvotes, etc. are all counted. The outmost SELECT is used so I can manipulate these also

Code

DECLARE @UID int = ##UserId##

DECLARE @Up      int = 1
DECLARE @Down    int = 2
DECLARE @Q       int = 10
DECLARE @A       int = 5
DECLARE @Comment int = 1

SELECT Score = Upvotes   * @Up +
          Downvotes * @Down +
          Questions * @Q +
          Answers   * @A +
          Comments  * @Comment,
       Upvotes, Downvotes, Questions, Answers, Comments
FROM (
  SELECT TOP 1
    (
      SELECT UpVotes
      From Users
      WHERE Id = @UID
    ) as Upvotes,
    (
      SELECT DownVotes
      From Users
      WHERE Id = @UID
    ) as Downvotes,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 1
    ) as Questions,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 2
    ) as Answers,
    (
      SELECT COUNT(*)
      FROM Comments
      WHERE UserId = @UID
    ) as Comments
  FROM Users, Comments, Posts
) data

I'd appreciate any comments on better ways to write this program, if I can restructure this better, my formatting, and really anything because I suck at SQL.

share|improve this question
1  
mashing snippets from SO into a program until it works sounds like a very simplistic description of what programmers do. I hope you do some analyzing and debugging in between. – Simon Forsberg Mar 5 at 16:12
1  
@SimonForsberg of course, I wasn't completely serious when I wrote that – Downgoat Mar 5 at 16:14
up vote 13 down vote accepted

TOP 1

When selecting values that correspond to Unique fields referenced by constants (OwnerUserId), you're only really selecting one field, meaning this is redundant.


UVN

Unexplained Variable Names

Don't use names like Q and A, they're confusing at best and unreadable at worst.

DECLARE @Q
DECLARE @A
DECLARE @Questions
DECLARE @Answers

Yo dawg, heard you like SELECTs

SELECT --...
FROM (
  SELECT TOP 1
    (
      SELECT UpVotes
      From Users
      WHERE Id = @UID
    ) as Upvotes,

SQL is not intended to be used like that.

You don't need to create pseudo tables to select from to store your data.

You can use a base table, and then specify certain fields calling other tables from that.

SELECT
  UpVotes + DownVotes as [Total Votes],
  (SELECT COUNT(*) FROM Posts WHERE OwnerUserId = Users.Id) as [Post Count]
FROM Users

Formatting

This formatting is a bit weird:

SELECT Score = Upvotes   * @Up +
          Downvotes * @Down +
          Questions * @Q +
          Answers   * @A +
          Comments  * @Comment,

You might want to break these up a little more.


Naming

These aren't the actual values, they're type weights, so I'd add the word weight to the end of the variable names:

DECLARE @Up      int = 1
DECLARE @Down    int = 2
DECLARE @Q       int = 10
DECLARE @A       int = 5
DECLARE @Comment int = 1

Into:

DECLARE @UpVotesWeight     int = 1
DECLARE @DownVotesWeight   int = 2
DECLARE @QuestionsWeight   int = 10
DECLARE @AnswersWeight     int = 5
DECLARE @CommentsWeight    int = 1
share|improve this answer
    
"confusing at best and unreadable at worst." Imo, unreadable and confused should be swapped – Downgoat Feb 26 at 4:11
8  
Best case scenario is confusion, worst case scenario is completely unreadable – Quill Feb 26 at 4:12

This is too much for a comment, adding to @Quill's answer:

In your Select you use FROM Users, Comments, Posts which is an unconstraint cross join, i.e. every row from every table A joined to every row from table B without join-condition, effectively multiplying the number of rows from each table in the answer set.

I just tried it and the number of rows per table was:

 53968 posts
121357 comments
 36457 users

resulting in 53,968 * 121,357 * 36,457 = 238,771,278,057,232 rows

Of course the optimizer is not that stupid (and there's a TOP 1) and actually creates that number of rows, but the plan (check the Include execution plan option & run it) is frightening.

Never ever write queries like that on a production system.

Change it to:

FROM (
  SELECT Upvotes, Downvotes,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 1
    ) as Questions,
    (
      SELECT COUNT(*)
      FROM Posts
      WHERE OwnerUserId = @UID and PostTypeId = 2
    ) as Answers,
    (
      SELECT COUNT(*)
      FROM Comments

    ) as Comments
  FROM Users -- no addtional tables
  WHERE Id = @UID
) data

See the modified query and compare execution plans :-)

share|improve this answer
3  
Really good catch on the cross-join, ++! – Phrancis Feb 26 at 12:14

Adding on to what was already stated, you could move your subqueries into CTE's for readability (and it might help you piece out each step in your code). We can also consolidate our request to Posts into one pass. I'm doing a LEFT JOIN since some users might not have Comments or Posts, and putting NULL Handling in to the calculation for the same reason.

DECLARE @UID INT = ##UserId##

DECLARE @UpVoteWeight   INT = 5
DECLARE @DownVoteWeight INT = 8
DECLARE @QuestionWeight INT = 25
DECLARE @AnswerWeight   INT = 10
DECLARE @CommentWeight  INT = 5

;WITH agg_posts AS (
  SELECT 
      OwnerUserId AS UserId,
      SUM(IIF(PostTypeID=1,1,0)) AS Questions,
      SUM(IIF(PostTypeID=2,1,0)) AS Answers
  FROM 
      Posts
  GROUP BY
      OwnerUserID
)
, agg_comments AS (
  SELECT
      UserId,
      COUNT(1) AS Comments
  FROM
      Comments
  GROUP BY
      UserId
)
SELECT 
    u.Id AS UserId,
    ISNULL(u.Upvotes,0)   * @UpVoteWeight +
    ISNULL(u.Downvotes,0) * @DownVoteWeight +
    ISNULL(p.Questions,0) * @QuestionWeight +
    ISNULL(p.Answers,0)   * @AnswerWeight +
    ISNULL(c.Comments,0)  * @CommentWeight AS Score,
    ISNULL(u.Upvotes,0) AS Upvotes, 
    ISNULL(u.Downvotes,0) AS Downvotes, 
    ISNULL(p.Questions,0) AS Questions, 
    ISNULL(p.Answers,0) AS Answers, 
    ISNULL(c.Comments,0) AS Comments
FROM
    Users u
LEFT JOIN
    agg_posts p ON p.UserId = u.Id
LEFT JOIN
    agg_comments c ON c.UserId = u.Id
WHERE
    u.Id = @UID
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.