Top users with infrequent posts

0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the top 500 users with an average of <X posts over Y days

Stack Overflow

Q&A for programmers

-- Top users with infrequent posts
-- Shows the top 500 users with an average of <X posts over Y days

DECLARE
  @NewestPostDate datetime,
  @MaxPostsPerWeek int,
  @DaysToCheck int

SET @MaxPostsPerWeek = ##MaxPostsPerWeek##
SET @DaysToCheck = ##DaysToCheck##

SELECT @NewestPostDate = MAX(CreationDate)
FROM Posts;

WITH TopUsers AS
(
  SELECT TOP ##UserCount## Id, DisplayName, Reputation
  FROM Users
  ORDER BY Reputation DESC
),
NewPostCounts AS
(
  SELECT
    OwnerUserId,
    COUNT(*) AS PostCount,
    COUNT(*) * 7.0 / @DaysToCheck AS AveragePostsPerWeek 
  FROM Posts
  WHERE CreationDate >= DATEADD(DAY, -@DaysToCheck, @NewestPostDate)
  GROUP BY OwnerUserId
)
SELECT u.Id, u.DisplayName, u.Reputation, c.PostCount, c.AveragePostsPerWeek 
FROM TopUsers u
INNER JOIN NewPostCounts c
  ON c.OwnerUserId = u.Id
WHERE c.AveragePostsPerWeek <= @MaxPostsPerWeek
ORDER BY c.AveragePostsPerWeek

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached