"So" leaderboards

0

Please login or register to vote for this query.

(click on this box to dismiss)

Ranking of users based on how many answers begin with a particular word or phrase.

Christianity

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 50 u.DisplayName 'Name', COUNT(p.Id) 'Opens With', Totals.Count
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 + '%'
GROUP BY u.DisplayName, Totals.Count
ORDER BY Count(p.Id) DESC

--SELECT DisplayName, @SoCount, @AllCount

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results