Analysis of number of images per post

1

Please login or register to vote for this query.

(click on this box to dismiss)

The Workplace Meta

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

SELECT
  (LEN(Body) - LEN(REPLACE(Body, '<img ', ''))) / 5 AS Images,
  COUNT(*) AS Frequency
  INTO #MyPosts FROM Posts
  WHERE OwnerUserId = ##UserId:int##
  GROUP BY LEN(Body) - LEN(REPLACE(Body, '<img ', ''));
  
SELECT
  (LEN(Body) - LEN(REPLACE(Body, '<img ', ''))) / 5 AS Images,
  COUNT(*) AS Frequency
  INTO #AllPosts FROM Posts
  GROUP BY LEN(Body) - LEN(REPLACE(Body, '<img ', ''));
  
SELECT #MyPosts.Images,
  COALESCE(#AllPosts.Frequency, 0) AS 'All users',
  COALESCE(#MyPosts.Frequency, 0) AS 'One user'
  FROM #MyPosts
  FULL OUTER JOIN #AllPosts
    ON #MyPosts.Images = #AllPosts.Images
  ORDER BY Images;

Enter Parameters

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