Number of days with a post/comment

1

Please login or register to vote for this query.

(click on this box to dismiss)

German Language Meta

Q&A about the site for speakers of German wanting to discuss the finer points of the language and translation

;  with dayspost_cte as
(
SELECT
  u.Id AS uid,
  COUNT (DISTINCT CAST(p.CreationDate AS DATE)) AS days
FROM Posts p INNER JOIN Users u ON u.id=p.OwnerUserId
GROUP BY u.Id
), dayscomment_cte as (
SELECT
  u.Id AS uid,
  COUNT (DISTINCT CAST(c.CreationDate AS DATE)) AS days
FROM Comments c INNER JOIN Users u ON u.id=c.UserId
GROUP BY u.Id
)

SELECT TOP(##num?100##)
  u.Id AS [User Link], u.CreationDate,
  p.days AS [Days Posted],
  ROUND(p.days*100.0/(DATEDIFF(day, u.CreationDate, CAST(GETDATE() AS Date))) ,2) AS [Percentage Posted],
  c.days AS [Days Commented],
  ROUND(c.days*100.0/(DATEDIFF(day, u.CreationDate, CAST(GETDATE() AS Date))) ,2) AS [Percentage Commented]
FROM Users u INNER JOIN dayspost_cte p ON u.Id=p.uid
  INNER JOIN dayscomment_cte c ON u.Id=c.uid
ORDER BY p.Days DESC

Enter Parameters

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