How to Get New Users Per Day in MySQL

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.

How To Enable MySQL Slow Query Log in MySQL

How To Get Last 12 Months Data in MySQL

How to create a read-only MySQL user?