What are the most popular first 3 words in question titles?

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT TOP 100 SUBSTRING(p.Title, 1, CHARINDEX(' ', p.Title, CHARINDEX(' ', p.Title, CHARINDEX(' ', p.Title) + 1) + 1)) AS First3Words, 
    COUNT(DISTINCT p.Id) AS Questions, 
    CAST(AVG(p.Score * 1.0) AS DECIMAL(10, 1)) AS AvgScore, 
    CAST(AVG(p.AnswerCount * 1.0) AS DECIMAL(10, 1)) AS AvgAnswerCount, 
    CAST(AVG(p.CommentCount * 1.0) AS DECIMAL(10, 1)) AS AvgCommentCount, 
    CAST(AVG(p.ViewCount * 1.0) AS DECIMAL(10, 1)) AS AvgViewCount, 
    CAST(AVG(p.FavoriteCount * 1.0) AS DECIMAL(10, 1)) AS AvgFavoriteCount
  FROM dbo.Posts p
  WHERE p.PostTypeId = 1
    AND CHARINDEX(' ', p.Title, CHARINDEX(' ', p.Title, CHARINDEX(' ', p.Title) + 1) + 1) > 0
  GROUP BY SUBSTRING(p.Title, 1, CHARINDEX(' ', p.Title, CHARINDEX(' ', p.Title, CHARINDEX(' ',p.Title) + 1) + 1))
  ORDER BY COUNT(DISTINCT p.Id) DESC;

Enter Parameters

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