Annual Top 12 most common "1st Word of Title" for Questions, and how it impacts Score & answerCount!

1

Please login or register to vote for this query.

(click on this box to dismiss)

Most common "first word of question title" by year, comparing Average Score and Answer Count. Note that "I Statements" are not good in this case! (Avg score is below zero even though it's a top-ten first word.)

Sports

Q&A for participants in team and individual sport activities

select
  year(creationdate) yr,
  SUBSTRING(lower(trim(title)),1,PATINDEX('%[^a-z]%',lower(trim(title)))-1) firstWord,
  score,
  answercount
into #fw
from posts
where 
  PATINDEX('%[^a-z]%',lower(trim(title)))>0
  and posttypeid=1 
  and closeddate is null 
  and year(creationdate)>=year(getdate())-##yearsBack:int?2##;
  
select 
  rank() OVER (PARTITION BY yr ORDER BY count(*) desc) AS Rank,
  format(yr,'0') yr,
  firstWord,
  format(count(*),'#,##0') cnt,
  format(avg(1.0*score),'0.00') avg_score,
  format(avg(1.0*answercount),'0.0') avg_num_answers
into #out
from #fw
where len(firstWord)>0
group by firstWord, yr
order by count(*) desc, FirstWord;

select
  yr [Year],
  Rank, 
  UPPER(LEFT(firstWord,1))+LOWER(SUBSTRING(firstWord,2,LEN(firstWord))) [1st Word], 
  cnt [#], 
  avg_score [Avg Score],
  avg_num_answers [Avg #Answers]
from #out
where rank<=12
order by yr, rank, firstWord

-- yearsBack: How many years back (before this year) to include?

Enter Parameters

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