DECLARE @TagName nvarchar(25) = 'r'; WITH topusers AS( S...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- User statistics on join date and last active date
-- For users active in tag 'r'
DECLARE @TagName nvarchar(25) = 'r';

WITH topusers AS(
    SELECT
        u.id, 
        COUNT(*) AS UpVotes,
        MAX(p.LastActivityDate) AS LastActivityDate,
        SUM(p.AnswerCount) AS AnswerCount, 
        SUM(p.CommentCount) AS CommentCount
        
    FROM Tags t with (nolock)
        INNER JOIN PostTags pt with (nolock)ON pt.TagId = t.id
        INNER JOIN Posts p with (nolock) ON p.ParentId = pt.PostId
        INNER JOIN Votes v with (nolock) ON v.PostId = p.Id and v.VoteTypeId = 2
        inner join Users u with (nolock) on u.Id = p.OwnerUserId
    WHERE 
        p.CommunityOwnedDate IS NULL
        and t.TagName = @TagName
    GROUP BY u.Id
)
SELECT
    topusers.id,
    u.DisplayName,
    topusers.UpVotes,
    u.location,
    u.CreationDate,
    u.Reputation,
    u.LastAccessDate,
    topusers.LastActivityDate,
    topusers.AnswerCount,
    topusers.CommentCount
FROM topusers
JOIN Users u on (topusers.id = u.id)

Enter Parameters

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