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.