How to Count New Users Per Day in MySQL

 

For every organization, keeping track of daily new user counts is crucial. You may use it to build day-by-day reports, count daily new users, and examine the overall number of registered users over time. Here's how to add new MySQL users every day.

How to Count New Users Per Day in MySQL

Here are the procedures for increasing MySQL's daily user count. It may be used to calculate the daily registration rate.

Assume you have a database called users(user id, date joined) that contains all user ids as well as the date that each user joined, registered, or signed up.

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 |
+-------------+---------+

Since there is just one record per user in this scenario, a date-wise count in SQL may be used to quickly add new users to MySQL. Here is the MySQL SQL query to count the number of records every day.

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       |
+-------------------+----------------+