Who likes that post?
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.