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 20 u.Id 'User Link', 
   ROUND((1.0 * COUNT(p.Id) / TotalAnswers.Count) * 100, 2) '% Catchphrase usage',
   COUNT(p.Id) 'Catchphrase openings',
   TotalAnswers.Count 'Total answers'
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) TotalAnswers ON TotalAnswers .OwnerUserId = u.Id
WHERE p.PostTypeId = 2
  AND p.Body LIKE '<p>' + @phrase + ' %'
  AND TotalAnswers.Count > 5
GROUP BY u.id, TotalAnswers.Count
ORDER BY '% Catchphrase usage' DESC

Enter Parameters

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