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 about the site for professional and independent game developers
CREATE TABLE #Numbers ( n int NOT NULL ); INSERT INTO #Numbers SELECT TOP 250 ROW_NUMBER() OVER (Order By Id) FROM Posts; CREATE TABLE #Titles (Title varchar(250) NOT NULL, Closed bit NOT NULL ); INSERT INTO #Titles (Title, Closed) SELECT TOP 700000 -- 25% ' ' + LOWER(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(CAST(P.Title as varchar(250)), ',', ' '), '?', ''), '*',''), '/', ''), ':', ''), '"', ''), '(', ''), ')', ''), '''', ''),'`',' '), '<', ''),' ',' ')) + ' ', 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' AND PH.PostHistoryTypeId = 10 WHERE P.Title IS NOT NULL AND P.PostTypeId = 1 ORDER BY P.Id DESC; -- reverse chronological creation CREATE TABLE #WordFrequency ( Word varchar(250) NOT NULL, TotalNum int NOT NULL, NumClosed int NOT NULL); WITH TitleWords (Word, Closed) AS ( SELECT SubString(Title, n + 1 , CHARINDEX(' ', Title, n + 1) - n ), Closed FROM #Titles T INNER JOIN #Numbers N ON SUBSTRING(T.Title, N.n, 1) = ' ' WHERE N.n < LEN(T.Title) - 1 ) INSERT INTO #WordFrequency(Word, TotalNum, NumClosed) SELECT TW.Word, COUNT(*), SUM(CAST(Closed AS int)) FROM TitleWords TW WHERE TW.Word <> '' GROUP BY TW.Word; SELECT * FROM (SELECT TOP 10 Reason = 'High Ratio' , Word , TotalNum , NumClosed , 1.0 * NumClosed/TotalNum AS Ratio FROM #WordFrequency WHERE TotalNum > 1 -- uncommon mispellings, really infrequently used words (2 mil posts) ORDER BY 1.0 * NumClosed/TotalNum DESC, NumClosed DESC) A UNION SELECT * FROM (SELECT TOP 10 Reason = 'High Occurrence' , Word , TotalNum , NumClosed , 1.0 * NumClosed/TotalNum AS Ratio FROM #WordFrequency WHERE 1.0 * NumClosed/TotalNum > .1 ORDER BY NumClosed DESC, 1.0 * NumClosed/TotalNum DESC) B; DROP TABLE #WordFrequency; DROP TABLE #Titles; DROP TABLE #Numbers;