# How to Calculate Retention Rate in SQL?

Retention Rate is defined as the number of customers who continue to use a product/service. It is difficult to calculate cohort retention analysis. Here’s how to calculate retention rate in SQL for customer retention analysis. You can use it to calculate retention rate in MySQL, PostgreSQL, SQL Server & Oracle. We will also look at the SQL query for customer retention. Retention rate is measured as the number of returning users, at a regular interval such as every week or month, grouped by their week of signup.

We will calculate retention by weekly cohort in SQL and end up with a table like the one below, which shows the number of customers who have logged in again after first signing up a few weeks ago, for each week of signing up.

## How to Calculate Retention Rate in SQL?

Here are the steps to calculate retention rate in SQL. Let’s say you have the following table that stores user_id and login_date of each user’s visit.

```mysql> create table login(login_date date,user_id int, id int not null auto_increment, primary key (id));

values('2020-01-01',10),('2020-01-02',12),('2020-01-03',15),
('2020-01-04',11),('2020-01-05',13),('2020-01-06',9),
('2020-01-07',21),('2020-01-08',10),('2020-01-09',10),
('2020-01-10',2),('2020-01-11',16),('2020-01-12',12),
('2020-01-13',10),('2020-01-14',18),('2020-01-15',15),
('2020-01-16',12),('2020-01-17',10),('2020-01-18',18),
('2020-01-19',14),('2020-01-20',16),('2020-01-21',12),
('2020-01-22',21),('2020-01-23',13),('2020-01-24',15),
('2020-01-25',20),('2020-01-26',14),('2020-01-27',16),
('2020-01-28',15),('2020-01-29',10),('2020-01-30',18);

+------------+---------+----+
| login_date | user_id | id |
+------------+---------+----+
| 2020-01-01 |      10 |  1 |
| 2020-01-02 |      12 |  2 |
| 2020-01-03 |      15 |  3 |
| 2020-01-04 |      11 |  4 |
| 2020-01-05 |      13 |  5 |
| 2020-01-06 |       9 |  6 |
| 2020-01-07 |      21 |  7 |
| 2020-01-08 |      10 |  8 |
| 2020-01-09 |      10 |  9 |
| 2020-01-10 |       2 | 10 |
| 2020-01-11 |      16 | 11 |
| 2020-01-12 |      12 | 12 |
| 2020-01-13 |      10 | 13 |
| 2020-01-14 |      18 | 14 |
| 2020-01-15 |      15 | 15 |
| 2020-01-16 |      12 | 16 |
| 2020-01-17 |      10 | 17 |
| 2020-01-18 |      18 | 18 |
| 2020-01-19 |      14 | 19 |
| 2020-01-20 |      16 | 20 |
| 2020-01-21 |      12 | 21 |
| 2020-01-22 |      21 | 22 |
| 2020-01-23 |      13 | 23 |
| 2020-01-24 |      15 | 24 |
| 2020-01-25 |      20 | 25 |
| 2020-01-26 |      14 | 26 |
| 2020-01-27 |      16 | 27 |
| 2020-01-28 |      15 | 28 |
| 2020-01-29 |      10 | 29 |
| 2020-01-30 |      18 | 30 |
+------------+---------+----+
```

We will be creating weekly cohort analysis. Depending on your product/service you can change it to monthly/daily.

We will be using MySQL to calculate retention rate in SQL. You can also calculate churn rate for PostgreSQL.

## 1. Bucket Visits By Week

To calculate retention rate in SQL, first, we will group each visit by its week of login.

```mysql> SELECT
user_id,
+---------+------------+
+---------+------------+
|       2 |          1 |
|       9 |          1 |
|      10 |          0 |
|      10 |          1 |
|      10 |          2 |
|      10 |          4 |
|      11 |          0 |
|      12 |          0 |
|      12 |          2 |
|      12 |          3 |
|      13 |          1 |
|      13 |          3 |
|      14 |          3 |
|      14 |          4 |
|      15 |          0 |
|      15 |          2 |
|      15 |          3 |
|      15 |          4 |
|      16 |          1 |
|      16 |          3 |
|      16 |          4 |
|      18 |          2 |
|      18 |          4 |
|      20 |          3 |
|      21 |          1 |
|      21 |          3 |
+---------+------------+
```

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

## 2. Calculate FIRST WEEK of login for each user

Next, to calculate retention rate in SQL, we need to calculate the first week of login for each user. We will simply use MIN function and GROUP BY to calculate first login week for each user

```mysql> SELECT
user_id,
GROUP BY user_id;
+---------+------------+
| user_id | first_week |
+---------+------------+
|       2 |          1 |
|       9 |          1 |
|      10 |          0 |
|      11 |          0 |
|      12 |          0 |
|      13 |          1 |
|      14 |          3 |
|      15 |          0 |
|      16 |          1 |
|      18 |          2 |
|      20 |          3 |
|      21 |          1 |
+---------+------------+
```

## 3. Merge the 2 tables for login_week and first_week

Next, we get login_week and first_week side by side for each user using the query below, with an INNER JOIN, to calculate retention rate in SQL.

```mysql> select a.user_id,a.login_week,b.first_week as first_week  from
(SELECT
user_id,
(SELECT
user_id,
GROUP BY user_id) b
where a.user_id=b.user_id;
+---------+------------+------------+
| user_id | login_week | first_week |
+---------+------------+------------+
|       2 |          1 |          1 |
|       9 |          1 |          1 |
|      10 |          0 |          0 |
|      10 |          1 |          0 |
|      10 |          2 |          0 |
|      10 |          4 |          0 |
|      11 |          0 |          0 |
|      12 |          0 |          0 |
|      12 |          2 |          0 |
|      12 |          3 |          0 |
|      13 |          1 |          1 |
|      13 |          3 |          1 |
|      14 |          3 |          3 |
|      14 |          4 |          3 |
|      15 |          0 |          0 |
|      15 |          2 |          0 |
|      15 |          3 |          0 |
|      15 |          4 |          0 |
|      16 |          1 |          1 |
|      16 |          3 |          1 |
|      16 |          4 |          1 |
|      18 |          2 |          2 |
|      18 |          4 |          2 |
|      20 |          3 |          3 |
|      21 |          1 |          1 |
|      21 |          3 |          1 |
+---------+------------+------------+
```

## 4. Calculate Week number

From here on, it is easy to calculate retention rate in SQL. Next, we calculate the difference between login_week and first_week to calculate week_number (number of week)

```mysql> select a.user_id,a.login_week,b.first_week as first_week,
(SELECT
user_id,
(SELECT
user_id,
GROUP BY user_id) b
where a.user_id=b.user_id;
+---------+------------+------------+-------------+
| user_id | login_week | first_week | week_number |
+---------+------------+------------+-------------+
|       2 |          1 |          1 |           0 |
|       9 |          1 |          1 |           0 |
|      10 |          0 |          0 |           0 |
|      10 |          1 |          0 |           1 |
|      10 |          2 |          0 |           2 |
|      10 |          4 |          0 |           4 |
|      11 |          0 |          0 |           0 |
|      12 |          0 |          0 |           0 |
|      12 |          2 |          0 |           2 |
|      12 |          3 |          0 |           3 |
|      13 |          1 |          1 |           0 |
|      13 |          3 |          1 |           2 |
|      14 |          3 |          3 |           0 |
|      14 |          4 |          3 |           1 |
|      15 |          0 |          0 |           0 |
|      15 |          2 |          0 |           2 |
|      15 |          3 |          0 |           3 |
|      15 |          4 |          0 |           4 |
|      16 |          1 |          1 |           0 |
|      16 |          3 |          1 |           2 |
|      16 |          4 |          1 |           3 |
|      18 |          2 |          2 |           0 |
|      18 |          4 |          2 |           2 |
|      20 |          3 |          3 |           0 |
|      21 |          1 |          1 |           0 |
|      21 |          3 |          1 |           2 |
+---------+------------+------------+-------------+
```

## 5. Pivot the result

Finally, we need to pivot the result, to calculate retention rate in SQL, and generate cohort table. In our pivot table, we will have one row for each first_week value, and one column for each week_number containing the number of users who have back after ‘n’ weeks to use your product/service. For this, we use the following query.

```mysql> select first_week,
SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0,
SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1,
SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2,
SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3,
SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4,
SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5,
SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6,
SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7,
SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8,
SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9

from  (

user_id,
user_id,
GROUP BY user_id) b
where a.user_id=b.user_id

) as with_week_number

group by first_week
order by first_week;
+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| first_week | week_0 | week_1 | week_2 | week_3 | week_4 | week_5 | week_6 | week_7 | week_8 | week_9 |
+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
|          0 |      4 |      1 |      3 |      2 |      2 |      0 |      0 |      0 |      0 |      0 |
|          1 |      5 |      0 |      3 |      1 |      0 |      0 |      0 |      0 |      0 |      0 |
|          2 |      1 |      0 |      1 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
|          3 |      2 |      1 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |      0 |
+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+

```

Now we know how to calculate retention rate in SQL. You can also the above queries to calculate retention rate in MySQL, PostgreSQL.

Finally, you can use a data visualization tool to plot the above retention cohort analysis in a table. Here’s a cohort retention table created using Ubiq.

By the way, if you want to create pivot tables, charts & dashboards from MySQL database, you can try Ubiq. We offer a 14-day free trial.