1

Please login or register to vote for this query.

(click on this box to dismiss)

Shows the total number of registered users for every month since site inception.

Q&A for user experience researchers and experts

-- Total number of registered users over time
-- Shows the total number of registered users for every month since site inception.

WITH c AS
(
    SELECT
        DATEPART(year, CreationDate) AS Year,
        DATEPART(month, CreationDate) AS Month
        FROM Users
),
d AS
(
    SELECT
        Year,
        Month,
        COUNT(*) OVER(PARTITION BY Year, Month) AS Count
        FROM c
),
e AS
(
    SELECT Year, Month, Count
        FROM d
        GROUP BY Year, Month, Count
),
f AS
(
    SELECT
        ROW_NUMBER() OVER(ORDER BY Year, Month) AS RowId,
        Year, Month, Count
        FROM e
)
SELECT
    Year,
    Month,
    (SELECT SUM(Count) FROM f WHERE f.RowId <= q.RowId) AS TotalCount
    FROM f q
    ORDER BY Year, Month

fork query

created jan 14 11 Jon Seigel