Stack Exchange User Activity Score

Question

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.

Answer 1

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

Answer 2

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 :-)

Answer 3

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