Is there a method to write a SQL query that returns cumulative results based on the count of another column?

Question

I have a query where I am counting the total number of new users signed up to a particular service each day since the service started.

So far I have:

SELECT DISTINCT CONVERT(DATE, Account_Created) AS Date_Created, 
COUNT(ID) OVER (PARTITION BY CONVERT(DATE, Account_Created)) AS New_Users
FROM My_Table.dbo.NewAccts
ORDER BY Date_Created

This returns:

Date_Created | New_Users
--------------------------
2020-01-01         1
2020-01-03         3
2020-01-04         2
2020-01-06         5
2020-01-07         9

What I would like is to return a third column with a cumulative total for each day starting from the beginning until the present. So the first day there was only one new user. On January 3rd, three new users signed up for a total of four since the beginning--so on and so forth.

Date_Created | New_Users | Cumulative_Tot
------------------------------------------
2020-01-01         1             1
2020-01-03         3             4
2020-01-04         2             6
2020-01-06         5             11
2020-01-07         9             20

My thought process was to involve the ROW_NUMBER() function so that I can separate and add each consecutive row together, though I am not sure if that is correct. My feeling is that I am probably thinking about this too hard and the logic is simply just escaping me at the moment. Thank you for any help.

Answer 1

As a starter: I would recommend aggregation rather than DISTINCT and a window count. This makes the intent clearer, and is likely more efficient.

Then, you can make use of a window sum to compute the cumulative count.

SELECT 
    CONVERT(DATE, Account_Created) AS Date_Created, 
    COUNT(*) AS New_Users
    SUM(COUNT(*)) OVER(ORDER BY CONVERT(DATE, Account_Created)) Cumulative_New_Users
FROM My_Table.dbo.NewAccts
GROUP BY CONVERT(DATE, Account_Created)
ORDER BY Date_Created