MySQL query to count cumulative user number based on activation date groupped by month

Question

I have following table and data:

CREATE TABLE tbl_users (
  `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `activation_date` DATETIME
  );

INSERT INTO tbl_users (id, activation_date) VALUES
    (1, '2020-01-15' ),
    (2, '2020-02-13' ),
    (3, '2020-02-15' ),
    (4, '2020-03-01' ),
    (5, '2020-03-03' ),
    (6, '2020-05-01' ),
    (7, '2020-06-01' ),
    (8, '2020-07-15' ),
    (9, '2020-08-15' ),
    (10, '2020-08-15' ),
    (11, '2020-08-19' );

I am looking for optimal way to count summary number of the users at the end of each month based on the activation date. for test data above output should look like below:

month   cumulative
1       1
2       3
3       5
4       5
5       6
6       7
7       8
8       11
9       11
10      11

I was trying with:

SELECT MONTH(activation_date) as month, COUNT(*) as cumulative 
FROM tbl_users 
WHERE activation_date >= :start GROUP BY month

but I get values for specific months instead of cumulative. Any idea how to improve the query? Or do I need to process it later in php? Thanks.

Answer 1

Since MySQL 8.0 you can use window function for this task:

SELECT DISTINCT
    MONTH(activation_date) as month, 
    SUM(1) over (order by MONTH(activation_date) )as cumulative 
FROM tbl_users 
WHERE activation_date >= :start 
;

SQL editor online

Result:

+-------+------------+
| month | cumulative |
+-------+------------+
|     1 |          1 |
|     2 |          3 |
|     3 |          5 |
|     5 |          6 |
|     6 |          7 |
|     7 |          8 |
|     8 |         11 |
+-------+------------+