1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for professional and independent game developers

declare @past int = 4;
declare @Today smalldatetime = getdate();
declare @After smalldatetime = DATEADD(week, @past * -1, @Today); 

with unit as (
  
  select top 1000
    id,creationDate
  from users u
  where 
    u.CreationDate > @after
    and reputation < 100
  order by 
    creationDate desc

),

-- retrieve posts

main as (

  select 
    p.id postId,u.id userId,score,title,p.body,p.tags,
      try_convert(xml,p.body) tmp,
    p.creationDate postDate, 
    u.creationDate signupDate 
  from posts p 
  inner join unit u on 
    p.OwnerUserId = u.Id and p.postTypeId = 1
  where 
    p.CreationDate > @after

),

-- post process text nodes
-- investigate better tokenisation alternative:
-- https://stackoverflow.com/a/18635337/24561636

post as (

  SELECT postId,
    REPLACE(LOWER(REPLACE(TRANSLATE(T2.Loc.value('.','nvarchar(1000)'), 
     '0123456789,.\/<>?;:"[]{}-=_+!@#$%^&*()`~', 
     '########################################'), 
     '#', '|')
     ),''' ',' ') text
    
  FROM main
  CROSS APPLY tmp.nodes('/p/text()') AS T2(Loc)   
  
),

cuts as (

  select postId, trim(value) as phrase from post 
   cross apply string_split(text,'|',1)

),

dist as (

  select top 1024
    phrase, count(distinct postId) questions 
  from cuts 
  where 
    len(phrase) > 5 and phrase like '% %'
  group by phrase
  order by questions desc,phrase
)

select 
  lag(value) over (order by ordinal) + ' ' +
  value + ' ' +
  lead(value) over (order by ordinal)
  as term,ordinal as idx from cuts
cross apply string_split(trim(phrase),' ',1)
where ordinal > 1

view current version

edited apr 24

created apr 24 Dennis.Leeftink