0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for musicians, students, and enthusiasts

SELECT TOP 10 Users.Id AS UserId, 
       ISNULL(PostsWithDeleted.PostCount, 0) AS PostCount,
       ISNULL(Comments.CommentCount, 0) AS CommentCount,
       ISNULL(PostsWithDeleted.PostCount, 0) + ISNULL(Comments.CommentCount, 0) AS TotalActivity
FROM Users
LEFT JOIN (
    SELECT OwnerUserId, COUNT(Id) AS PostCount
    FROM PostsWithDeleted
    WHERE CreationDate >= DATEADD(YEAR, -5, GETDATE()) 
      AND OwnerUserId != -1
    GROUP BY OwnerUserId
) AS PostsWithDeleted ON Users.Id = PostsWithDeleted.OwnerUserId
LEFT JOIN (
    SELECT UserId, COUNT(Id) AS CommentCount
    FROM Comments
    WHERE CreationDate >= DATEADD(YEAR, -5, GETDATE())
    GROUP BY UserId
) AS Comments ON Users.Id = Comments.UserId
WHERE Users.Id != -1
ORDER BY TotalActivity DESC;

fork query

edited nov 2

created nov 2 anonymous