0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for followers of the Hindu religion and those interested in learning more about Hinduism

-- 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;

fork query

edited oct 13

created oct 13 Akarsh.Jain