This is a newer query to return the list of words that have the highest ratio of being used in 'closed as non-constructive' vs all posts. Although I've tried two different methods of splitting, the sheer amount of data and my rustiness with SQL optimization is causing all the methods I have tried to timeout, even when doing a pre-query for just words in closed posts. Currently running most recent 25% of posts and sorting by ratio desc, so words with largest chance of being closed.
Q&A for professional and enthusiast programmers
CREATE TABLE #Titles (PostId bigint NOT NULL, Title nvarchar(250) NOT NULL, Closed bit NOT NULL ); INSERT INTO #Titles (PostId, Title, Closed) SELECT TOP 5000 P.Id, LOWER(Replace(Replace(Replace(Replace(Replace(Replace(P.Title, ',', ' '), '?', ''), ' ',' '), '/', ''), ':', ''), '"', '')), CASE WHEN PH.id IS NOT NULL THEN 1 ELSE 0 END FROM Posts P LEFT JOIN PostHistory PH ON PH.PostId = P.Id AND PH.Comment = '3' WHERE P.Title IS NOT NULL; CREATE TABLE #WordFrequency ( Word nvarchar(250) NOT NULL, TotalNum int NOT NULL, NumClosed int NOT NULL); WITH TitleWords (PostId, Word, Rem, Closed) AS ( SELECT T.PostId, SUBSTRING(T.Title, 1, CHARINDEX(' ', T.Title + ' ')), SUBSTRING(T.Title, CHARINDEX(' ', T.Title + ' ') + 1, LEN(T.Title)), T.Closed FROM #Titles T UNION ALL SELECT PostId, SUBSTRING(Rem, 1, CHARINDEX(' ', Rem + ' ')), SUBSTRING(Rem, CHARINDEX(' ', Rem + ' ') + 1, LEN(Rem)), Closed FROM TitleWords WHERE LEN(Rem) > 0 ) INSERT INTO #WordFrequency(Word, TotalNum, NumClosed) SELECT TW.Word, COUNT(*), SUM(CASE WHEN Closed = 1 Then 1 ELSE 0 END) FROM TitleWords TW GROUP BY TW.Word; SELECT TOP 10 Word , TotalNum , NumClosed , 1.0 * NumClosed/TotalNum AS Ratio FROM #WordFrequency ORDER BY 1.0 * NumClosed/TotalNum DESC; DROP TABLE #WordFrequency; DROP TABLE #Titles;