Ranking of users based on how many answers begin with a particular word or phrase.
Q&A for committed Christians, experts in Christianity and those interested in learning more
-- 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',
Totals.Count 'Answer count',
CAST(Totals.Count as float) / COUNT(p.Id)
FROM Users u
INNER JOIN Posts p ON p.OwnerUserId = u.Id
(SELECT COUNT(*) 'Count', OwnerUserId
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 Count(p.Id) DESC