WITH QA AS ( SELECT p.OwnerUserId as UserId, sum(case whe...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH QA AS (
  SELECT
    p.OwnerUserId as UserId,
    sum(case when p.PostTypeId = 1 then 1 else 0 end) as Questions,
    sum(case when p.PostTypeId = 2 then 1 else 0 end) as Answers
  FROM
    Posts p
  WHERE
    p.PostTypeId IN (1,2) 
  GROUP BY
    p.OwnerUserId
)

SELECT
  UserId as [User Link],
  AVG(CAST(CommentCount as float)) as [Average Comments/Post],
  Count(*) as Posts
FROM (
  SELECT
    UserId,
    COUNT(*) as CommentCount
  FROM
    Comments as c
    WHERE EXISTS (
                SELECT 1
                  FROM QA
                 WHERE UserId = c.UserId
                   AND QA.Questions * 10 < QA.Answers)
  GROUP BY
    UserId,
    PostId
) as CommentCounts
GROUP BY UserId
HAVING count(*) > 90
ORDER BY
  [Average Comments/Post] DESC

Enter Parameters

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