SQL Server cumulative user count over days

Question

I have an user table like this:

id | createdAt                      | name  ...
---+--------------------------------+-----------
1    2017-04-12 13:19:37.287 +00:00    X
2    2017-04-12 13:19:37.287 +00:00    Y
3    2017-04-12 13:19:37.287 +00:00    Z
4    2017-04-13 13:19:37.287 +00:00    T
5    2017-04-13 13:19:37.287 +00:00    W
6    2017-04-14 13:19:37.287 +00:00    W
7    2017-04-14 13:19:37.287 +00:00    W

What I need is cumulative user count per day, like this:

day        | count
-----------+--------
2017-04-12    3
2017-04-13    5
2017-04-14    7

I tried grouping days with this SQL statement, but it only gets me the records of the day not cumulative

SELECT 
    CAST(createdAt AS DATE) AS date, COUNT(*) AS userCount 
FROM 
    dbo.Users 
GROUP BY 
    CAST(createdAt AS DATE) 
ORDER BY 
    CAST(createdAt AS DATE) DESC

Any help is appreciated

Thanks

Answer 1

In SQL Server 2012+, you would use a cumulative sum:

SELECT CAST(createdAt AS DATE) as date, COUNT(*) as userCount,
       SUM(COUNT(*)) OVER (ORDER BY CAST(createdAt AS DATE)) as cumulative_count
FROM dbo.Users u
GROUP BY CAST(createdAt AS DATE)
ORDER BY CAST(createdAt AS DATE) DESC;

In earlier versions, some another method is needed, such as a correlated subquery:

WITH u as (
      SELECT CAST(createdAt AS DATE) as date, COUNT(*) as userCount
      FROM dbo.Users u
      GROUP BY CAST(createdAt AS DATE)
     )
SELECT u.*,
       (SELECT SUM(usrCount)
        FROM u u2
        WHERE u2.date <= u.date
       ) as cumulative_count
FROM u
ORDER BY date DESC;