Most popular first words of question titles

0

Please login or register to vote for this query.

(click on this box to dismiss)

Skeptics

Q&A for scientific skepticism

-- Used to evaluate a hypothesis that certain types of question (e.g.
-- Is, Are, Was, Were, Do, Does, etc.) do better than others (e.g. How, Who,
-- Should)
--
-- Groups the questions by the first word of each title, discards the rare
-- ones, and then returns how often they occur, what score they average and
-- how often they get closed.

SELECT 
    SUBSTRING(title, 1, (CHARINDEX(' ', title + ' ')-1)) AS FirstWord,
    COUNT(*) AS Popularity,
    AVG(score) AS AverageScore,
    SUM(
      CASE  
         WHEN ClosedDate IS NULL THEN 0.0
         ELSE 1.0
         END) AS NumberClosed,
    ROUND(100*AVG(
      CASE  
         WHEN ClosedDate IS NULL THEN 0.0
         ELSE 1.0
         END), 2) AS PercentageClosed
FROM Posts
WHERE PostTypeId = 1
GROUP BY 
  SUBSTRING(title, 1, (CHARINDEX(' ',title + ' ')-1))
HAVING 
  COUNT(*) > 1 
ORDER BY
  COUNT(*) DESC

Enter Parameters

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