Most abrupt users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Or 'concise', depending how you look at it

Christianity Meta

Q&A about the site for committed Christians, experts in Christianity and those interested in learning more

-- Most abrupt users
-- Or 'concise', depending how you look at it

SELECT TOP 100
  U.DisplayName,
  P.AvgPost,
  P.MinPost,
  P.NumPost,
  C.AvgComment,
  C.MinComment,
  C.NumComment,
  Co.ShortestComment
FROM

(SELECT
  OwnerUserId,
  Avg(Len(Body)) AvgPost,
  Min(Len(Body)) MinPost,
  Count(*) NumPost
FROM Posts
GROUP BY OwnerUserId) P

INNER JOIN 

(SELECT
  UserId,
  Avg(Len(Text)) AvgComment,
  Min(Len(Text)) MinComment,
  Count(*) NumComment
FROM Comments
GROUP BY UserId) C

 ON P.OwnerUserId = C.UserId

INNER JOIN Users U
 ON C.UserId = U.Id

INNER JOIN

(SELECT
  UserId,
  Len(Text) LenComment,
  Min(Text) ShortestComment
FROM Comments
GROUP BY UserId, Len(Text)) Co

  ON C.UserId = Co.UserId
  AND C.MinComment = Co.LenComment

WHERE Len(U.DisplayName) > 0 AND NumComment > 5 AND NumPost > 5

ORDER BY P.AvgPost + C.AvgComment

Enter Parameters

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