How to Calculate Daily Active Users (DAU) in MySQL

Last updated on May 17th, 2020 at 06:31 am

Daily Active Users (DAU) is a useful KPI for every business. It helps you understand how many people are actually using your product/service everyday. Here’s how to calculate daily active users (DAU) in MySQL.

How to Calculate Daily Active Users (DAU) in MySQL

You will mainly need 2 columns for this purpose – user id and timestamp(when user account was created). Let’s say you have stored user information in the following table users(id, date_joined)

mysql> create table users (
id int(11) NOT NULL auto_increment,
date_joined timestamp,
PRIMARY KEY (id)
);


mysql> insert into users values
(1, '2019-12-01 2:00:00'),
(2,'2019-12-01 2:30:00'),
(3,'2019-12-02 10:00:00'),
...;

mysql> select * from users;
+----+---------------------+
| id | date_joined         |
+----+---------------------+
|  1 | 2019-12-01 02:00:00 |
|  2 | 2019-12-01 02:30:00 |
|  3 | 2019-12-01 10:00:00 |
|  4 | ...                 |
+----+---------------------+

Here’s a simple SQL query to calculate daily active users (DAU) in MySQL for preceding day

SELECT COUNT(DISTINCT id)
    FROM users
    WHERE date_joined > NOW() - INTERVAL 1 DAY;
+--------------------+
|         DAU        |
+--------------------+
|        120         |
+--------------------+

You can plot the above DAU as a single number chart on your dashboard.

Also check out How to Calculate Weekly Active Users (WAU) in MySQL.

If you want to add some kind of filter (e.g status=4) to count only specific users, then you can add the condition to WHERE clause in the above SQL query

SELECT COUNT(DISTINCT id) as DAU
    FROM users
    WHERE date_joined > NOW() - INTERVAL 1 DAY and status=4;
+--------------------+
|        DAU         |
+--------------------+
|        31          |
+--------------------+

Also read: How to Calculate Monthly Active Users (MAU) in MySQL

If you want to calculate daily DAU, that is, daily active users every day, then here’s the SQL query to calculate daily active users (DAU) in MySQL.

SELECT DATE(date_joined) AS DAY,
       COUNT(id) AS DAU
    FROM users
    GROUP BY DATE(date_joined);

+------------+--------+
|   DAY      |  DAU   |
+------------+--------+
| 2019-01-01 |  70    |
| 2019-01-02 |  77    |
| 2019-01-03 |  71    |
| 2019-01-04 |  70    |
| 2019-01-05 |  ...   |
+------------+--------+

You can use a charting tool to plot the daily DAU value in a line chart to get the growth trend for your business. Here’s an example of a line chart created using Ubiq