# Who likes that post?

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
JOIN Posts


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.Score AS [Score],
Count(1) AS [Favorited By]
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.

• Have you factored in the users that "favorite" their own question(s)? Jul 17, 2016 at 1:28
• @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... Jul 17, 2016 at 9:05

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

• 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. Jul 19, 2016 at 16:33
• 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? Jul 19, 2016 at 16:35
• 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. Jul 19, 2016 at 16:50