10 Most Common Words Showing in Posts Closed as Not Constructive


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

Amateur Radio

Q&A for amateur radio enthusiasts

WITH TitleWords (PostId, Word, Rem) AS (
   SELECT TOP 10 P.Id, SUBSTRING(Title, 1, CHARINDEX(' ', Title + ' ')), SUBSTRING(Title, CHARINDEX(' ', Title + ' ') + 1, LEN(Title))
   FROM Posts P
      INNER JOIN PostHistory PH ON PH.PostId = P.Id
   WHERE PH.Comment = '3'
   SELECT PostId, SUBSTRING(Rem, 1, CHARINDEX(' ', Rem + ' ')), SUBSTRING(Rem, CHARINDEX(' ', Rem + ' ') + 1, LEN(Rem))
   FROM TitleWords
   WHERE LEN(Rem) > 0
FROM TitleWords

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results