1

Please login or register to vote for this query.

(click on this box to dismiss)

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 ', ''));
  
DECLARE @MyPostsCount FLOAT, @AllPostsCount FLOAT;
SELECT @MyPostsCount  = SUM(Frequency) FROM #MyPosts;
SELECT @AllPostsCount = SUM(Frequency) FROM #AllPosts;
  
SELECT COALESCE(#MyPosts.Images, #AllPosts.Images),
  ROUND(100 * COALESCE(#AllPosts.Frequency, 0) / @AllPostsCount, 2) AS 'All users',
  ROUND(100 * COALESCE(#MyPosts.Frequency,  0) / @MyPostsCount,  2) AS 'One user'
  FROM #MyPosts
  FULL OUTER JOIN #AllPosts
    ON #MyPosts.Images = #AllPosts.Images
  WHERE COALESCE(#MyPosts.Images, #AllPosts.Images) < ##MaximumNumberOfImages:int?10##
  ORDER BY 1

fork query

edited mar 15 22

created mar 15 22 Glorfindel