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 about the site for people interested in improving and participating in the patent system

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

fork query

created nov 30 10 Brant