Q&A for people studying math at any level and professionals in related fields
declare @past int = 3; declare @Today smalldatetime = getdate(); declare @After smalldatetime = DATEADD(week, @past * -1, @Today); with unit as ( select top 50000 id,creationDate from users u where u.CreationDate > @after and reputation between 2 and 100 order by creationDate desc ), -- retrieve batch 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 -- order by p.id,postDate desc ), -- post process text nodes -- better replace alternative: -- https://stackoverflow.com/a/18635337/24561636 post as ( SELECT postId, LOWER(REPLACE(TRANSLATE(T2.Loc.value('.','nvarchar(100)'), '0123456789,.\/<>?;:"[]{}-=_+!@#$%^&*()`~', '########################################'), '#', '|')) text FROM main CROSS APPLY tmp.nodes('/p/text()') AS T2(Loc) ), dist as ( select value as term,count(postId) freq from post cross apply string_split(text,'|',1) where len(value) > 5 and trim(value) like '% %' group by value ) select top 256 * from dist order by freq desc