Most active users

0

Please login or register to vote for this query.

(click on this box to dismiss)

Writing

Q&A for the craft of professional writing, including fiction, non-fiction, technical, scholarly, and commercial writing

-- Most active users (action every X min)
-- Users who spend A LOT of time posting, editing, commenting and voting.
-- WARNING: Long exec time! (> 1 min)
-- Code from: https://data.stackexchange.com/stackoverflow/query/27385/most-active-users-action-every-x-min

DECLARE @dt DateTime = getdate()

SELECT TOP 20 UserId AS [User Link], datediff(mi,CreationDate,@dt)/sum(c) as ActionEveryXMin
FROM (
    SELECT UserId, count(*) AS c
    FROM PostHistory
    WHERE UserId > 0
    GROUP BY UserId
    UNION ALL
    SELECT UserId, count(*) AS c
    FROM Comments
    WHERE UserId > 0
    GROUP BY UserId
    UNION ALL
    SELECT UserId, count(*) AS c
    FROM Votes
    WHERE UserId > 0
    GROUP BY UserId
) a
INNER JOIN Users on Id = UserId
WHERE dateadd(dd,1,CreationDate) < @dt -- users have to be at least a day old
GROUP BY UserId, CreationDate
ORDER BY ActionEveryXMin

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results