SQL count per day and add together all following days

Question

I'm trying to write a SQL query to count users per day and add the result together. I'm stuck because I don't know how to dynamically add the results together.

Table:

UserId | CreateDate
-------+----------------------------
  1    | 2016-06-23 13:59:24.0000000
  2    | 2016-06-23 15:59:24.0000000
  3    | 2016-06-24 05:59:24.0000000
  ...

I have following query to count the registrations per day.

SELECT 
    CONVERT(date, u.CreateDate) as [Date], Count(UserId) as [Count] 
FROM 
    User as u
GROUP BY 
    CONVERT(date, u.CreateDate)
ORDER BY
    [Date]

I have no idea how to add up the results. I hope that SQL Server 2008 R2 has a built-in function for my case. Or do I need to create temp tables?

Assuming Day 1 has 2 registrations and Day 2 has 1 registration and Day 3 has 4 registrations, I would like to receive a result like this:

2016-06-23 | 2
2016-06-24 | 2 + 1 = 3
2016-06-25 | 3 + 4 = 7

I would need a dynamic solution because the query will have another restriction by date => Where CreateDate bewteen x and y.

Thanks Ruedi

Answer 1

with redshift database

select [Date],sum([Count]) over(order by [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
from (SELECT CONVERT(date, u.CreateDate) as [Date], Count(UserId) as [Count] 
      FROM User as u
      GROUP BY CONVERT(date, u.CreateDate)
      ) x
order by 1