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.

• Simon Forsberg: 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.
• Downgoat: @SimonForsberg of course, I wasn't completely serious when I wrote that

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

• Downgoat: "confusing at best and unreadable at worst." Imo, unreadable and confused should be swapped
• Quill: Best case scenario is confusion, worst case scenario is completely unreadable

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

• Phrancis: Really good catch on the cross-join, ++!

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