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
-- "So" leaderboards
-- Ranking of users based on how many answers begin with a particular word or phrase.
DECLARE @Phrase nvarchar(50)
SET @Phrase = 'so'
DECLARE @UserId int, @SoCount int, @AllCount int
--SET @UserId = 102
SELECT TOP 15 u.DisplayName 'Name', COUNT(p.Id) 'Opens With', Totals.Count 'Answer count'
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 + '%'
GROUP BY u.DisplayName, Totals.Count
ORDER BY Count(p.Id) DESC