1

Please login or register to vote for this query.

(click on this box to dismiss)

Total new users that joined [site] each year-month over the given site's history

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

DECLARE @SEDEUpdated AS DATETIME = (SELECT TOP 1 LastAccessDate FROM Users ORDER BY LastAccessDate DESC)

SELECT
  DATEFROMPARTS(YEAR(u.CreationDate), MONTH(u.CreationDate), 1) AS [Year-Month],
  SUM(COUNT(*)) OVER(ORDER BY DATEFROMPARTS(YEAR(u.CreationDate), MONTH(u.CreationDate), 1) ROWS UNBOUNDED PRECEDING) AS [Total Users]
FROM Users u
WHERE u.CreationDate < DATEFROMPARTS(YEAR(@SEDEUpdated), MONTH(@SEDEUpdated), 1) -- Exclude current month (incomplete data)
GROUP BY DATEFROMPARTS(YEAR(u.CreationDate), MONTH(u.CreationDate), 1)
ORDER BY DATEFROMPARTS(YEAR(u.CreationDate), MONTH(u.CreationDate), 1) ASC

fork query

edited jul 21 21

created may 20 21 zcoop98