Number of posts for users last seen X months ago

0

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

SELECT
  Months AS 'Months since user last seen', 
  Posts AS 'Posts at exactly',
  SUM(Posts) OVER (ORDER BY Months DESC) AS 'Posts at or before'
FROM (
SELECT
  DATEDIFF(mm,Users.LastAccessDate,GETDATE()) AS 'Months',
  COUNT(*) AS 'Posts'
FROM Users
INNER JOIN Posts ON Posts.OwnerUserId = Users.Id
GROUP BY DATEDIFF(mm,Users.LastAccessDate,GETDATE())
--ORDER BY DATEDIFF(mm,Users.LastAccessDate,GETDATE())

) preliminary
ORDER BY Months

Enter Parameters

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