How to Calculate Weekly Active Users (WAU) in MySQL

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

Weekly Active Users (WAU) is a useful key performance indicator (KPI) to track for every online business. It gives you an idea as to how many people use your product/service at least once a week. Here’s how to calculate Weekly Active Users (WAU) in MySQL.

How to Calculate Weekly Active Users (WAU) in MySQL

Let’s say you have the following table users(user_id, last_login). last_login is the timestamp of the last time a user logged in. Some systems also store this timestamp as modified_date, updated_at, etc.

mysql> create table users(user_id int, last_login datetime);

mysql> insert into users(user_id, last_login) values(1,'2020-03-01 10:00:00'),
     (2,'2020-03-02 09:00:00'),(3,'2020-03-03 14:00:00'),(4,'2020-03-04 11:00:00'),
     (5,'2020-03-05 12:00:00'),(6,'2020-03-06 20:00:00'),(7,'2020-03-07 21:00:00'),
     (8,'2020-03-08 12:00:00'),(9,'2020-03-09 20:00:00'),(10,'2020-03-10 21:00:00'),
     (11,'2020-03-11 12:00:00'),(12,'2020-03-12 20:00:00'),(13,'2020-03-13 21:00:00'),
     (14,'2020-03-13 12:00:00'),(15,'2020-03-15 20:00:00'),(16,'2020-03-16 21:00:00');

mysql> select * from users;
+---------+---------------------+
| user_id | last_login          |
+---------+---------------------+
|       1 | 2020-03-01 10:00:00 |
|       2 | 2020-03-02 09:00:00 |
|       3 | 2020-03-03 14:00:00 |
|       4 | 2020-03-04 11:00:00 |
|       5 | 2020-03-05 12:00:00 |
|       6 | 2020-03-06 20:00:00 |
|       7 | 2020-03-07 21:00:00 |
|       8 | 2020-03-08 12:00:00 |
|       9 | 2020-03-09 20:00:00 |
|      10 | 2020-03-10 21:00:00 |
|      11 | 2020-03-11 12:00:00 |
|      12 | 2020-03-12 20:00:00 |
|      13 | 2020-03-13 21:00:00 |
|      14 | 2020-03-13 12:00:00 |
|      15 | 2020-03-15 20:00:00 |
|      16 | 2020-03-16 21:00:00 |
+---------+---------------------+

Here’s the SQL query to calculate Weekly Active Users (WAU) in MySQL, for past 1 week.

mysql> SELECT COUNT(DISTINCT user_id)
         FROM users
         WHERE last_login > NOW() - INTERVAL 1 WEEK;
+-------------------------+
| COUNT(DISTINCT user_id) |
+-------------------------+
|                      16 |
+-------------------------+

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

If you want to add filters (e.g status=4) to your query, you can add it to the WHERE clause, as shown below in bold.

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

If you want to calculate Weekly Active Users (WAU) for every week in your data, use the following SQL query. In this case, we simply use the WEEK function to aggregate users based on their last_login values.

mysql> SELECT WEEK(last_login) AS WEEK,
            COUNT(user_id) AS WAU
         FROM users
         GROUP BY WEEK(last_login);
+------+-----+
| WEEK | WAU |
+------+-----+
|    9 |   7 |
|   10 |   7 |
|   11 |   2 |
|   12 |  ...|
+------+-----+

You can easily customize the above query to calculate Weekly Active Users (WAU) in MySQL, and plot them on a line chart.

If you want to calculate how many users return to your website every week, then here’s how to calculate retention rate in SQL.

If you want to calculate Weekly Active Users for each day, that is, active users for preceding 6 days, for each day, then you can use the following query. Here we also calculate DAU (Daily active users) alongside WAU.

mysql> SELECT d.day
          , COUNT(DISTINCT u.user_id) AS wau
          , COUNT(DISTINCT IF(u.day=d.day,u.user_id,NULL)) AS dau
       FROM ( SELECT DATE(k.last_login) AS `day`
                FROM users k
               GROUP BY `day`
            ) d
       JOIN ( SELECT DATE(l.last_login) AS `day`
                   , l.user_id
                FROM users l
               GROUP BY `day`, l.user_id
            ) u
         ON u.day <= d.day AND u.day > DATE_ADD(d.day, INTERVAL -7 DAY)
      GROUP BY d.day
      ORDER BY d.day;
+------------+-----+-----+
| day        | wau | dau |
+------------+-----+-----+
| 2020-03-01 |   1 |   1 |
| 2020-03-02 |   2 |   1 |
| 2020-03-03 |   3 |   1 |
| 2020-03-04 |   4 |   1 |
| 2020-03-05 |   5 |   1 |
| 2020-03-06 |   6 |   1 |
| 2020-03-07 |   7 |   1 |
| 2020-03-08 |   7 |   1 |
| 2020-03-09 |   7 |   1 |
| 2020-03-10 |   7 |   1 |
| 2020-03-11 |   7 |   1 |
| 2020-03-12 |   7 |   1 |
| 2020-03-13 |   8 |   2 |
| 2020-03-15 |   7 |   1 |
| 2020-03-16 |   7 |   1 |
+------------+-----+-----+

In the above query, for each day, we count the number of distinct users who logged in that day and preceding 6 days.

Now you have seen different ways to calculate weekly active users (WAU) in MySQL. You can customize them as per your requirement.

You can also use a charting tool to plot weekly active users in bar chart and share it with your team. Here’s an example of bar chart that shows WAU, created using Ubiq