Catchphrase leaderboard

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

-- 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',
  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

Enter Parameters

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