Total Questions, Answers, and Comments of 1000 most recently active users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Software Engineering

Q&A for professionals, academics, and students working within the systems development life cycle

-- Total Questions, Answers, and Comments of 1000 most recently active users
 WITH RecentUsers AS
  (SELECT Posts.OwnerUserId,
          MAX(Posts.LastActivityDate) AS LastActivityDate,
          SUM(CASE
                  WHEN Posts.PostTypeId = 1 THEN 1
                  ELSE 0
              END) AS Questions,
          SUM(CASE
                  WHEN Posts.PostTypeId = 2 THEN 1
                  ELSE 0
              END) AS Answers
   FROM Posts
   GROUP BY Posts.OwnerUserId),
      CommentCounts AS
  (SELECT Comments.UserId,
          COUNT(*) AS Comments
   FROM Comments
   GROUP BY Comments.UserId)
SELECT TOP 1000 RecentUsers.OwnerUserId AS [User Link],
           RecentUsers.Questions,
           RecentUsers.Answers,
           CommentCounts.Comments,
           RecentUsers.LastActivityDate
FROM RecentUsers
INNER JOIN CommentCounts ON CommentCounts.UserId = RecentUsers.OwnerUserId
ORDER BY RecentUsers.LastActivityDate DESC

Enter Parameters

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