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.

Q&A for those using, extending or developing Emacs

-- 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
  -- try to remove punctuation from the question
  AND REPLACE(REPLACE(REPLACE(p.Body, ',', ' '), ':', ' '), '.', ' ') LIKE '<p>' + @phrase + ' %'
  AND TotalAnswers.Count > 5
GROUP BY u.id, TotalAnswers.Count
ORDER BY '% Catchphrase usage' DESC

fork query

created nov 30 10 Brant