0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

WITH RecentPosts AS (
    SELECT
        p.OwnerUserId,
        COUNT(*) AS PostCount
    FROM
        Posts p
    WHERE
        p.CreationDate >= DATEADD(DAY, -30, GETDATE())
        AND p.OwnerUserId IS NOT NULL
    GROUP BY
        p.OwnerUserId
),
RecentComments AS (
    SELECT
        c.UserId,
        COUNT(*) AS CommentCount
    FROM
        Comments c
    WHERE
        c.CreationDate >= DATEADD(DAY, -30, GETDATE())
        AND c.UserId IS NOT NULL
    GROUP BY
        c.UserId
),
UserSample AS (
    SELECT
        TOP 200
        u.Id,
        u.DisplayName,
        u.Reputation,
        u.Location,
        u.CreationDate,
        rp.PostCount,
        COALESCE(rc.CommentCount, 0) AS CommentCount
    FROM
        Users u
        LEFT JOIN RecentPosts rp ON u.Id = rp.OwnerUserId
        LEFT JOIN RecentComments rc ON u.Id = rc.UserId
    WHERE
        rp.PostCount IS NOT NULL
        AND u.Location IS NOT NULL -- Ensures users with a Location are included
    ORDER BY
        rp.PostCount DESC
)
SELECT
    Id,
    DisplayName,
    Reputation,
    Location,
    CreationDate,
    PostCount,
    CommentCount
FROM
    UserSample;

view current version

edited dec 2

created dec 2 jon.doe45850