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, @Today); 

with unit as (
  
  select top 1000000
    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)

),

-- trigrams (warning: slow)

/*term as (

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

),

tris as (

  select t1.postId,t1.phrase + ' ' + t2.phrase + ' ' + t3.phrase phrase from term t1
    left join term t2 on t1.postId = t2.postId and t1.sig = t2.sig and t1.ordinal = t2.ordinal -1
    left join term t3 on t1.postId = t3.postId and t1.sig = t3.sig and t1.ordinal = t3.ordinal -2   
  -- order by t1.postId,t1.sig,t1.ordinal

),

fuse as (

  select * from cuts
   union all
  select * from tris

),*/

-- phrase inversion method

/*calc as (


  select postId, checksum(text) sig, 
    trim(value) as term,
    count(postId) over(partition by value) mag,
    ordinal as ord from post 
  cross apply string_split(replace(text,'|',''),' ',1)


),

tier as (

  select *,percent_rank() over(order by mag desc,term) pos 
  from calc

),

fuse as (

  select 
    postId,
    string_agg(iif(pos > 0.025 and pos < 0.2 or pos > 0.8, '|' + term,term),' ') 
    within group (order by ord) text

  from tier 
  group by postId,sig

),

stat as (

 select top 1024 trim(value) as phrase,count(postId) mag from fuse
  cross apply string_split(text,'|')
  where trim(value) like '% %'
  group by trim(value) order by mag desc

),*/

dist as (

  select top 1024
    phrase, count(distinct postId) questions 
  from cuts -- fuse -- if using tris
  where 
    len(phrase) > 6 and trim(phrase) like '% % %' 
    and trim(phrase) not like '% % % % % %' 
  group by phrase
  order by questions desc,phrase

)

select * from dist order by phrase

fork query

edited apr 28

created apr 24 Dennis.Leeftink