Tag Wikis that are Too Short

0

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace

Q&A for members of the workforce navigating the professional setting

/*Finds all tags with wikis that need some work
  based on the totally arbitrary factors of:
   * summary/excerpt under 40 characters
   * full tag wiki under 100 characters
 TODO: search the text in w.Body and flag it if there are not certain formatting tags
*/

SELECT
  Tags.TagName as [Name]
  ,Tags.Count as [Number of Posts]
  ,CASE 
    WHEN LEN(ex.Body) < 50 THEN 'Summary Too Short'
    WHEN LEN(w.Body) < 100 THEN 'Wiki Too Short'
    --WHEN w.Body NOT LIKE '%[-*\>([#`]%' THEN 'Wiki Needs Formatting'
    ELSE NULL END as [Problem with Tag]
  ,LEN(ex.Body) as [Tag Summary Length]
  --,LEN(ex.Body) - LEN(REPLACE(ex.Body, ' ', '')) as [Words in Tag Summary]
  ,LEN(w.Body) as [Tag Wiki Length]
  --,LEN(w.Body) - LEN(REPLACE(w.Body, ' ', '')) as [Words in Tag Wiki]
FROM Tags
LEFT JOIN Posts ex
  ON Tags.ExcerptPostId = ex.Id AND ex.PostTypeID = 4 --TagWikiExcerpt
LEFT JOIN Posts w
  ON Tags.WikiPostId = w.Id AND w.PostTypeID = 5 --TagWiki
LEFT JOIN TagSynonyms ts
  ON Tags.TagName = ts.SourceTagName 

WHERE 
  ts.TargetTagName IS NULL
  AND (
    LEN(ex.Body) < 40 -- Tag Summary under 50 characters
    OR
    LEN(w.Body) < 100 -- Full Tag Wiki under 100 characters
    --OR 
    --w.Body NOT LIKE '%[-*\>([#`]%' -- no markdown styling :(
  )
ORDER BY Tags.Count DESC

Enter Parameters

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