Last updated on May 17th, 2020 at 06:06 am
It is very important to get count of new users per day for every business. You can use it to count daily new users, analyze total number of registered users by date and even create day wise report. Here’s how to get new users per day in MySQL.
How to Get New Users Per Day in MySQL
Here are the steps to get new users per day in MySQL. You can use it to count registered users per day.
Let’s say you have a table users(user_id, date_joined) that stores all user id’s along with their date of joining/registration/signup.
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 |
+-------------+---------+
Bonus Read : How to Get Last 3 Months Sales Data in MySQL
In this case, you have just 1 record per user so you can easily get new users in MySQL by doing a date wise count in SQL. Here’s the SQL query to count records per day in MySQL.
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 |
+-------------------+----------------+
In the above query, we simply use a DATE aggregate function to count daily number of registered users.
Bonus Read : How to Get Records of Current Month in MySQL
If you want to filter data based on specific conditions you can add a WHERE clause as shown below.
mysql> select date(date_joined),count(*)
from users
WHERE <condition>
group by date(date_joined);
Bonus Read : How to Import CSV File into MySQL Workbench
If you also want to calculate cumulative total users per day, then here’s the SQL query to get new users per day in MySQL. We will use the result of above query as the input to calculate total number of registered new users per day.
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 user count.
After you get new users 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.
Hopefully, you can get new users 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.