Get running total of users for each year

Question

I would like to calculate the total number of users that uses my application each year. both new and returning users.

I created this script below but it only shows total number of users for only 1 year. I would like to see the total for the years before (<= 2010-12-31). also note the years before can be more that 3.

SELECT '2010-12-31' as date, count(*) as year
        FROM users AS c 
        WHERE FORMAT(c.first_joined, 'yyyyMMdd') <= '2010-12-31 ' 
        AND status = 'ACTIVE'

first_joined: this is the date the user first entered the system total: this is the total of both new and returning users

results:

date	total
2010-12-31	8,617

Expected results will providing a running total of previous and current years:

date	total
2007-12-31 (1,000 joined in 2007)	1,000
2008-12-31 (200 joined in 2008)	1,200
2009-12-31 (1,000 joined in 2009)	2,200
2010-12-31 (6,417 joined in 2010)	8,617

Answer 1

You can use group by operator with DATEPART() function and then use window function to get the data you wants.

see the following code as example:

WITH yearly_new_data AS (
SELECT
     DATEPART(year,c.first_joined) as dt, 
     count(*) as total
FROM users AS c 
WHERE c.first_joined < CAST('20110101' AS DATE)
      AND status = 'ACTIVE'
GROUP BY
     DATEPART(year,c.first_joined)
)
SELECT
    dt,
    SUM(TOTAL) OVER (order by dt) AS total
FROM
    yearly_new_data