Adam Levitt Adam Levitt - 28 days ago 9
SQL Question

SQL Grouped Results with Counts and Cumulative Counts

I have the following data in my 'user' table:

user_id | create_timestamp
1 2017-08-01
2 2017-08-01
3 2017-08-02
4 2017-08-03
5 2017-08-03
6 2017-08-03
7 2017-08-04
8 2017-08-04
9 2017-08-04
10 2017-08-04


I want to create a SQL query that has three columns:
1. Grouped results by create_timestamp
2. A count of the results by date
3. A cumulative count as the date goes on.

Here's what the result set should look like:

create_timestamp daily cumulative
2017-08-01 2 2
2017-08-02 1 3
2017-08-03 3 6
2017-08-04 4 10

Answer Source

You can use this query.

DECLARE @UserLog TABLE (user_id INT , create_timestamp DATE)
INSERT INTO @UserLog
VALUES
(1,'2017-08-01'),
(2,'2017-08-01'),
(3,'2017-08-02'),
(4,'2017-08-03'),
(5,'2017-08-03'),
(6,'2017-08-03'),
(7,'2017-08-04'),
(8,'2017-08-04'),
(9,'2017-08-04'),
(10,'2017-08-04')

;WITH T AS (
    SELECT create_timestamp, COUNT(*) daily  FROM @UserLog
    GROUP BY create_timestamp)
    SELECT 
        create_timestamp, 
        daily, 
        SUM(daily) OVER( ORDER BY create_timestamp ASC  
                            ROWS UNBOUNDED PRECEDING ) cumulative  
FROM T

Result

create_timestamp daily       cumulative
---------------- ----------- -----------
2017-08-01       2           2
2017-08-02       1           3
2017-08-03       3           6
2017-08-04       4           10