7
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ Have you factored in the users that "favorite" their own question(s)? \$\endgroup\$ – Phrancis Jul 17 '16 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 '16 at 9:05
3
\$\begingroup\$

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.

|improve this answer|||||
\$\endgroup\$
  • \$\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 '16 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 '16 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 '16 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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