Who likes that post?

Question

Following on from my last question, I've been continuing to look at what information is available from the SE Data Explorer, I thought it would be interesting to know who else had favorited a question. Which led me to this query:

DECLARE @FavoriteType TINYINT = 5;

SELECT
       Votes.UserId AS [User Link],
       Votes.PostId AS [Post Link]
FROM Votes
JOIN Posts
  On Votes.PostId = Posts.Id
WHERE Votes.PostId = ##QuestionID##
AND Votes.VoteTypeId = @FavoriteType
ORDER BY Votes.CreationDate DESC

Whilst this is vaguely interesting, it's not really all that useful. However, I thought what might be useful is to know what other questions those users might have favorited. If enough of them favorite another question then since we already have something in common, it's probably an interesting question. With that in mind, I came up with this query:

DECLARE @FavoriteType TINYINT = 5;

SELECT
  TOP 100
  Posts.Id AS [Post Link],
  Posts.Score AS [Score],
  Count(1) AS [Favorited By]
FROM Votes [Favorited Post]
JOIN Votes [Other Favorites]
  On [Favorited Post].UserId = [Other Favorites].UserId
JOIN Posts
  On [Other Favorites].PostId = Posts.Id
WHERE [Favorited Post].PostId = ##PostID##
AND [Favorited Post].VoteTypeId = @FavoriteType
AND [Other Favorites].VoteTypeId = @FavoriteType
GROUP BY 
  Posts.Id, Posts.Score
Order by
  [Favorited By] desc

I've encountered various DBA's that were adamant about using COUNT(1) or COUNT(*), so I'm not really sure what the current flavour of the month is. Happy for feedback on either of the queries.

Answer 1

Overall, very good

I inspected your query closely and did not find anything standing out that could really be improved much. Here are some good points I found:

• Your formatting is consistent and the indentation makes it clear how your code is structured.

• You used a clear variable for Favorite type instead of magic numbers.

• Your aliases are clear and descriptive.

• There are no unnecessary operations, joins or sub-queries that I could find.

---

Here are a few very minor points to consider.

• Using AS operator for table aliases/correlation names. Here is an answer on Stack Overflow that covers the topic.

• You could use a small query with your variable to make the intent a bit more clear, like DECLARE @FavoriteType TINYINT = (SELECT Id FROM VoteTypes WHERE Name = 'Favorite'); although this could impact performance slightly, if you have a whole bunch of variables like this. With just a few though the performance difference is negligible.

• As for the COUNT(1) vs. COUNT(*), honestly there is no difference. Either way the query plan optimizer will execute them identically because they are both non-nullable expressions. See more details on Stack Overflow and a similar answer on Database Administrators which also covers COUNT(some_field) which behaves differently.