Stack Exchange User Activity Score

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  * 
2  * 
10 * 
5  * 
1  * 

The actual values of what ` 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.

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

StackExchange Code Review Q#121148, answer score: 13