10 Most Common Words Showing in Posts Closed as Not Constructive

2

Please login or register to vote for this query.

(click on this box to dismiss)

Less than performant query that takes all the words (sort of) that show up in post titles that have been closed a not constructive (I think) and selects the 10 that occur the most frequently. Added a filter for common words, but what it probably needs to do is analyze words that were and were not included in closed posts, then return only the top 10 that had no instances of non-constructive (or below a threshold?) Replace the "TOP 100 PERCENT" with a much smaller number, 1000 took about 40s when I ran it, 100 was about 300ms

Philosophy Meta

Q&A about the site for those interested in the study of the fundamental nature of knowledge, reality, and existence

CREATE TABLE #Titles (PostId bigint, Title nvarchar(250) );

INSERT INTO #Titles (PostId, Title)
SELECT TOP 100 PERCENT P.Id, LOWER(Replace(Replace(Replace(Replace(Replace(P.Title, ',', ' '), '?', ''), '  ',' '), '/', ''), ':', ''))
FROM Posts P
   INNER JOIN PostHistory PH ON PH.PostId = P.Id
WHERE PH.Comment = '3';

CREATE TABLE #StuffToIgnore ( Word varchar(250) );
INSERT INTO #StuffToIgnore
VALUES ('it')
       , ('for')
       , ('to')
       , ('is')
       , ('a')
       , ('the')
       , ('and')
       , ('of')
       , ('are')
       , ('as')
       , ('or')
       , ('i')
       --, ('what')
       --, ('how')
       ;       

WITH TitleWords (PostId, Word, Rem) AS (
   SELECT PostId, SUBSTRING(Title, 1, CHARINDEX(' ', Title + ' ')), SUBSTRING(Title, CHARINDEX(' ', Title + ' ') + 1, LEN(Title))
   FROM #Titles T
   
   UNION ALL
   
   SELECT PostId, SUBSTRING(Rem, 1, CHARINDEX(' ', Rem + ' ')), SUBSTRING(Rem, CHARINDEX(' ', Rem + ' ') + 1, LEN(Rem))
   FROM TitleWords
   WHERE LEN(Rem) > 0
)
SELECT TOP 10 TW.Word, COUNT(PostId) AS NumOccurred
FROM TitleWords TW
   LEFT JOIN #StuffToIgnore STI ON STI.Word = TW.Word
WHERE STI.Word IS NULL
GROUP BY TW.Word
ORDER BY COUNT(PostId) DESC;

DROP TABLE #Titles;
DROP TABLE #StuffToIgnore;

Enter Parameters

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