declare @tot_hnq as decimal declare @tot_not_hnq decimal ...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Mathematics

Q&A for people studying math at any level and professionals in related fields

declare @tot_hnq as decimal
declare @tot_not_hnq decimal

;with hnq_words as (
select title
,  replace(words.value,'?','') value
from posts p
inner join posthistory ph on ph.postid = p.id
cross apply string_split(title, ' ') words
where posthistorytypeid = 53
and posttypeid = 1
and len(value) > 3
)
, 
non_hnq_words as
(
select title
,  words.value value
from posts p
cross apply string_split(title, ' ') words
left outer join posthistory ph 
             on ph.postid = p.id 
             and posthistorytypeid = 53
where posttypeid = 1
and ph.id is null
and len(value) > 3
),
tot_hnq as (
select cast(count(*) as decimal) cnt from hnq_words
),
tot_not_hnq as 
(select cast(count(*) as decimal) cnt from non_hnq_words)


select h.word
     , hnq
     , non_hnq
     , (hnq/(select cnt from tot_hnq)) * 100 [% hnq]
     , (non_hnq/(select cnt from tot_not_hnq)) * 100 [% non hnq]
     , ((hnq/(select cnt from tot_hnq)) - (non_hnq/(select cnt from tot_not_hnq))) * 100 [diff]
from (
select lower(h.value) [word]
, count(*) [HNQ]
from hnq_words h
group by lower(h.value)
) h
left outer join 
(
select lower(nhw.value) [word]
, count(*) [non_HNQ]
from non_hnq_words nhw
group by lower(nhw.value)
) n on n.[word] = h.[word]
order by diff desc

Enter Parameters

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