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;

       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;

  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
  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.

  • \$\begingroup\$ Have you factored in the users that "favorite" their own question(s)? \$\endgroup\$
    – Phrancis
    Jul 17, 2016 at 1:28
  • \$\begingroup\$ @Phrancis I haven't, but I'm not sure it matters. I'm not looking at why they have it as a 'favorite', I'm guessing that the 'favorite'overlaps will be interesting. In the example results above, one of the overlaps is for a question with -36 votes, this is likely for a different reason to the others (maybe close vote or badge related) but is still interesting... \$\endgroup\$
    – forsvarir
    Jul 17, 2016 at 9:05

1 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.

  • \$\begingroup\$ Thanks for the review, I find your insights really useful. It's funny, but until you pointed it out I hadn't even noticed that I was using AS for column aliases but not for tables. I did think about using the VoteTypes table but in the end I decided that the Id was probably less likely to change than the Name so I'd just use that. \$\endgroup\$
    – forsvarir
    Jul 19, 2016 at 16:33
  • \$\begingroup\$ I do have a side question... You added the sql-server tag to my last question, so I put it on this one, but @Quill changed it to t-sql, which one (or both) do you think I should use if I ask future questions of a similar type? \$\endgroup\$
    – forsvarir
    Jul 19, 2016 at 16:35
  • 1
    \$\begingroup\$ t-sql is the more general term for this particular version/dialect of SQL. sql-server is the database engine (it's not the only one that uses t-sql). In your case it would be fine to have both. \$\endgroup\$
    – Phrancis
    Jul 19, 2016 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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