Ranking of users based on how many answers begin with a particular word or phrase.
Q&A about the site for people interested in improving and participating in the patent system
-- Catchphrase leaderboard -- Ranking of users based on how many answers begin with a particular word or phrase. DECLARE @Phrase nvarchar(50) SET @Phrase = '##Phrase##' SELECT TOP 15 u.DisplayName 'Name', COUNT(p.Id) 'Opens With', 1.0 * Totals.Count 'Answer count', Totals.Count / COUNT(p.Id) 'Percentage' FROM Users u INNER JOIN Posts p ON p.OwnerUserId = u.Id INNER JOIN (SELECT COUNT(*) 'Count', OwnerUserId FROM Posts WHERE PostTypeId = 2 GROUP BY OwnerUserId) Totals ON Totals.OwnerUserId = u.Id WHERE p.PostTypeId = 2 AND p.Body LIKE '<p>' + @phrase + '%' AND Totals.Count > 10 GROUP BY u.DisplayName, Totals.Count ORDER BY Percentage DESC