Mysql – How to find the retention of users for every month in MySQL

MySQL

There's a table with user_id, order_id, timestamp. The timestamp is in the format "2019-12-23 06:32:43".

Some of the users have made multiple orders across months (starting from june to december). How do I find out the number of retained users/retention month over month?

Please note that there's no signup/acquisition date and we need to work with the timestamp of first transaction for every user in order to find the respective retention.


I don't know how to create a table here.

The table preview is as follows:

user_id || transaction_id || timestamp || device_category

user_1 || transaction_1 || 2018-06-17 13:56:23 || iPhone

user_2 || transaction_2 || 2018-06-23 03:28:12 || Android

user_3 || transaction_3 || 2018-07-11 19:21:37 || Android

user_1 || transaction_43 || 2018-08-20 11:24:01 || iPhone

user_27 || transaction_99 || 2018-09-11 02:21:05 || Android

user_1 || transaction_114 || 2018-10-13 22:32:21 || iPhone

user_2 || transaction_119 || 2018-10-15 13:56:23 || Android

Best Answer

Maybe this will get you started?

SELECT user_id,
       MIN(timestamp) AS first_purchase_date,
       MAX(timestamp) AS latest_purchase_date
    FROM tbl
    GROUP BY user_id

Simply use SHOW CREATE TABLE tablename to get the structure.