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 academics and those enrolled in higher education
SET STATISTICS TIME ON
CREATE TABLE #Numbers ( n int NOT NULL );
INSERT INTO #Numbers
SELECT TOP 250 ROW_NUMBER() OVER (Order By Id) FROM Posts;
CREATE TABLE #Titles (PostId bigint NOT NULL, Title nvarchar(250) NOT NULL, Closed bit NOT NULL );
INSERT INTO #Titles (PostId, Title, Closed)
SELECT TOP 100000
' ' + LOWER(Replace(Replace(Replace(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;
print 'here 2';
CREATE TABLE #WordFrequency ( Word nvarchar(250) NOT NULL, TotalNum int NOT NULL, NumClosed int NOT NULL);
WITH TitleWords (PostId, Word, Closed) AS (
SubString(Title, n + 1 , CHARINDEX(' ', Title, n + 1) - n ),
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)
SUM(CAST(Closed AS int))
FROM TitleWords TW
WHERE TW.Word <> ''
GROUP BY TW.Word;
print 'here 3';
SELECT TOP 10
, 1.0 * NumClosed/TotalNum AS Ratio
ORDER BY 1.0 * NumClosed/TotalNum DESC, NumClosed DESC;
print 'here 4';
DROP TABLE #WordFrequency;
DROP TABLE #Titles;
DROP TABLE #Numbers;
SET STATISTICS TIME OFF