0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

; with firstposts_cte AS (
SELECT
  u.Id AS uid,
  MIN(p.CreationDate) AS mindate,
  DATEADD(week, 1, MIN(p.CreationDate)) AS maxdate
FROM Users u INNER JOIN Posts p ON p.OwnerUserId=u.Id
WHERE p.PostTypeId IN (1, 2)
GROUP BY u.Id
)

SELECT 
  eomonth(p.CreationDate),
  COUNT(p.Id) AS [Posts]
FROM
  firstposts_cte f
  INNER JOIN
  Posts p
  ON f.uid=p.OwnerUserId
  INNER JOIN
  PostTags pt ON pt.PostId=p.Id
WHERE
  p.PostTypeId IN (1, 2)
  AND p.CreationDate BETWEEN f.mindate
  AND f.maxdate
  AND pt.TagId=22
GROUP BY eomonth(p.CreationDate)
ORDER BY eomonth(p.CreationDate)

fork query

edited jul 26 22 Abdul.Aziz.Barkat

created jan 18 21 Martin20528