find 10 words which, in titles, are most likely to cause a question to be closed as Not Constructive


Please login or register to vote for this query.

(click on this box to dismiss)

DevOps Meta

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

set nocount on;

declare @title nvarchar(4000), @postId int, @nc int;
declare @words table (postid int, word nvarchar(450), nc int);
declare @word nvarchar(4000), @to int, @i int  = 0, @sampled int = 0;

-- with no right to declare functions, must use an iterative cursor solution
declare crs cursor read_only forward_only for
select top(1000000) lower(p.Title), p.Id, 
   case when h.PostId is null then 0 else 1 end as nc
from Posts p
left outer join (
  select PostId 
  from PostHistory 
  where Comment = '3' and PostHistoryTypeId = 10) h
on p.Id = h.PostId
where p.PostTypeId = 1
order by p.Id;

open crs;

fetch next from crs into @title, @postId, @nc;
while 0 = @@fetch_status
  -- Analyze a NC closed title 
  -- or 10% of other titles
  if 1 = @nc or rand() > 0.90 
    --raiserror (N'At title: %s', 0,1, @title);
    while 1=1
      -- discard everything before first a-z
      set @to = patindex(N'[a-z]%', @title);
      if (0 = @to)
         --raiserror (N'No more words in %s', 0,3, @title);
      set @title = substring(@title, @to, 8000);
      -- find next non a-z
      set @to = patindex(N'%[^a-z]%', @title);
      if @to = 0
         -- this is the last word in the title
        set @to = len(@title);
      set @word = substring(@title, 1, @to-1);
      -- ignore anything less than 3 chars
      if len(@word) > 3
        insert into @words (postid, word, nc)
        values  (@postId, @word, @nc);
      set @title = substring(@title, @to+1, 8000);
      --raiserror (N'Found word: ''%s'' Left: ''%s''', 0,1, @word, @title);
    set @sampled += 1;
  fetch next from crs into @title, @postId, @nc;
  set @i += 1;
  if 0 =  @i % 1000
    raiserror (N'Processed %d titles (sampled %d). At postId: %d', 0, 2, 
      @i, @sampled, @postId);
raiserror (N'Exit at %d titles (sampled %d). At postId: %d', 0, 2, 
      @i, @sampled, @postId);

close crs;
deallocate crs;

 select word, count(*) as cnt
 into #nc_words
  from @words 
  where nc = 1
  group by nc, word;
create unique clustered index nc_words_word 
  on #nc_words(word);

select word, count(*) as cnt
  into #population_words
  from @words 
  where nc = 0
  group by nc, word;
create unique clustered index population_words_word 
  on #population_words(word);

select nc.word, nc.cnt as nc_count, g.cnt as good_count
from #nc_words nc
left outer join #population_words g
  on nc.word = g.word
where nc.word not in (
select top (10) percent pw.word
  from #population_words pw
  order by pw.cnt desc)
order by nc.cnt desc;

Enter Parameters

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