Last updated on May 17th, 2020 at 06:06 am
Daily cumulative total users is a very useful KPI metric for analyzing user growth, for every business. However, since there is no built-in function to calculate running total in MySQL, here’s the SQL query to get cumulative total users per day, also known as rolling sum by date or running total of users.
How to Get Cumulative Total Users Per Day in MySQL
Here are the steps to get cumulative total users per day in MySQL. Let’s say you have the following table users(date_joined,user_id) that contains all the user ids with their date of registration/sign up/joining for your business.
mysql> create table users(date_joined date,user_id int);
mysql> insert into users(date_joined,user_id)
values('2020-04-28',213),
('2020-04-28',214),
('2020-04-30',215),
('2020-04-28',216),
('2020-04-28',217),
('2020-04-30',218),
('2020-04-28',219),
('2020-04-28',220),
('2020-04-30',221),
('2020-05-01',222),
('2020-05-01',222),
('2020-05-01',223),
('2020-05-04',224),
('2020-05-04',225),
('2020-05-04',226),
('2020-05-04',226),
('2020-05-04',227),
('2020-05-04',228),
('2020-05-05',229),
('2020-05-05',230),
('2020-05-05',231),
('2020-05-05',232),
('2020-05-06',233),
('2020-05-06', 234);
mysql> select * from users;
+-------------+---------+
| date_joined | user_id |
+-------------+---------+
| 2020-04-28 | 213 |
| 2020-04-28 | 214 |
| 2020-04-30 | 215 |
| 2020-04-28 | 216 |
| 2020-04-28 | 217 |
| 2020-04-30 | 218 |
| 2020-04-28 | 219 |
| 2020-04-28 | 220 |
| 2020-04-30 | 221 |
| 2020-05-01 | 222 |
| 2020-05-01 | 222 |
| 2020-05-01 | 223 |
| 2020-05-04 | 224 |
| 2020-05-04 | 225 |
| 2020-05-04 | 226 |
| 2020-05-04 | 226 |
| 2020-05-04 | 227 |
| 2020-05-04 | 228 |
| 2020-05-05 | 229 |
| 2020-05-05 | 230 |
| 2020-05-05 | 231 |
| 2020-05-05 | 232 |
| 2020-05-06 | 233 |
| 2020-05-06 | 234 |
+-------------+---------+
First, we look at an SQL query to calculate the daily number of sign ups
mysql> select date(date_joined),count(user_id)
from users
group by date(date_joined);
+-------------------+----------------+
| date(date_joined) | count(user_id) |
+-------------------+----------------+
| 2020-04-28 | 6 |
| 2020-04-30 | 3 |
| 2020-05-01 | 3 |
| 2020-05-04 | 6 |
| 2020-05-05 | 4 |
| 2020-05-06 | 2 |
+-------------------+----------------+
Next, here’s the SQL query to get cumulative total users per day in MySQL. We will use the above SQL as a subquery to get running total in SQL.
mysql> set @csum := 0;
mysql> select date_joined,new_users, (@csum := @csum + new_users) as total_users
from (
select date(date_joined) as date_joined,count(user_id) as new_users
from users
group by date(date_joined)
) as temp
order by date_joined;
+-------------+-----------+-------------+
| date_joined | new_users | total_users |
+-------------+-----------+-------------+
| 2020-04-28 | 6 | 6 |
| 2020-04-30 | 3 | 9 |
| 2020-05-01 | 3 | 12 |
| 2020-05-04 | 6 | 18 |
| 2020-05-05 | 4 | 22 |
| 2020-05-06 | 2 | 24 |
+-------------+-----------+-------------+
In the above query, we set a temporary variable csum to 0. Then for each row we use it to calculate and store the cumulative sum of users.
After you get cumulative total users per day in MySQL, you can plot it on a line chart or dashboard using a reporting tool like Ubiq and share it with your team. Here’s a line chart created using Ubiq.
If you want to get a conditional running total in MySQL, then you can add a WHERE clause in the subquery, as per your requirement.
mysql> set @csum := 0;
mysql> select date_joined,new_users, (@csum := @csum + new_users) as total_users
from (
select date(date_joined) as date_joined,count(user_id) as new_users
from users
WHERE <condition>
group by date(date_joined)
) as temp
order by date_joined;
You can easily modify the above SQL query, to calculate cumulative total users by week, month, quarter and year. Let us look at them one by one. We will use DATE_FORMAT function to easily get week, month from date columns.
Bonus Read : How to Calculate Revenue in MySQL
How to Get Cumulative Total Users Per Week in MySQL
Here’s the SQL query to get cumulative total users by week in MySQL. In the SQL query to get cumulative total users per day, you just need to change the aggregation function in subquery, from date(date_joined) to date_format(date_format,’%U’).
mysql> set @csum := 0;
mysql> select date_joined,new_users, (@csum := @csum + new_users) as total_users
from (
select date_format(date_joined,'%U') as date_joined,
count(user_id) as new_users
from users
group by date_format(date_joined,'%U')
) as temp
order by date_joined;
In the above query, you can also use WEEK function instead of DATE_FORMAT, that is, use WEEK(date_joined) instead of DATE_FORMAT(date_joined,’%U’)
Bonus Read : How to Get Records of Current Month in MySQL
How to Get Cumulative Total Users Per Month in MySQL
Here’s the SQL query to get cumulative total users by month in MySQL. In the SQL query to get cumulative total users per day, you just need to change the aggregation function in subquery, from date(date_joined) to date_format(date_format,’%b’).
mysql> set @csum := 0;
mysql> select date_joined,new_users, (@csum := @csum + new_users) as total_users
from (
select date_format(date_joined,'%b') as date_joined,
count(user_id) as new_users
from users
group by date_format(date_joined,'%b')
) as temp
order by date_joined;
Bonus Read : How to Get Last 3 Months Sales Data in MySQL
How to Get Cumulative Total Users Per Quarter in MySQL
Here’s the SQL query to get cumulative total users by quarter in MySQL. In the SQL query to get cumulative total users per day, you just need to change the aggregation function in subquery, from date(date_joined) to quarter(date_format).
mysql> set @csum := 0;
mysql> select date_joined,new_users, (@csum := @csum + new_users) as total_users
from (
select quarter(date_joined) as date_joined,
count(user_id) as new_users
from users
group by quarter(date_joined)
) as temp
order by date_joined;
How to Get Cumulative Total Users Per Year in MySQL
Here’s the SQL query to get cumulative total users by year in MySQL. In the SQL query to get cumulative total users per day, you just need to change the aggregation function in subquery, from date(date_joined) to year(date_format).
mysql> set @csum := 0;
mysql> select date_joined,new_users, (@csum := @csum + new_users) as total_users
from (
select year(date_joined) as date_joined,
count(user_id) as new_users
from users
group by year(date_joined)
) as temp
order by date_joined;
That’s it! You can try the above SQL queries to get cumulative total users per day in MySQL. If you want to create charts, dashboards & reports from MySQL database, you can try Ubiq. We offer a 14-day free trial.
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.