Q&A about the site for musicians, students, and enthusiasts
-- Step 1: Get the counts of posts and comments for each user WITH PostCounts AS ( SELECT OwnerUserId AS UserId, COUNT(*) AS PostCount FROM Posts WHERE CreationDate >= DATEADD(year, -5, GETDATE()) GROUP BY OwnerUserId ), CommentCounts AS ( SELECT UserId, COUNT(*) AS CommentCount FROM Comments WHERE CreationDate >= DATEADD(year, -5, GETDATE()) GROUP BY UserId ) -- Step 2: Combine and aggregate counts SELECT TOP 10 u.Id, u.DisplayName, COALESCE(pc.PostCount, 0) AS PostCount, COALESCE(cc.CommentCount, 0) AS CommentCount, (COALESCE(pc.PostCount, 0) + COALESCE(cc.CommentCount, 0)) AS TotalActivity FROM Users u LEFT JOIN PostCounts pc ON u.Id = pc.UserId LEFT JOIN CommentCounts cc ON u.Id = cc.UserId ORDER BY TotalActivity DESC;