How to find whether the same customers who ordered this month also ordered the next month?

Question

I have an orders table

Order_id  User_id  Order_date
 1        32        2020-07-19
 2        24        2020-07-21
 3        27        2020-07-27
 4        24        2020-08-14
 5        32        2020-08-18
 6        32        2020-08-19
 7        58        2020-08-20

Now I want to find how many of the users ordered in 1st month also ordered in the next month. In this case, user_id's 32,24,27 ordered in 7th month but only 24 and 32 ordered in the next month.

I want the result to be like :

Date   Retained_Users  Total_users
2020-07     Null            3
2020-08      2              3

I'm lost here. Can someone please help me with this?

Answer 1

In MySQL 8.0, you can do this with window functions:

select 
    order_month, 
    count(distinct case when cnt_orders_last_month > 0 then user_id end) retained_users,
    count(distinct user_id) total_users
from (
    select 
        user_id, 
        date_format(order_date, '%Y-%m-01') as order_month,
        count(*) over(
            partition by user_id
            order by date(date_format(order_date, '%Y-%m-01'))
            range between interval 1 month preceding and interval 1 day preceding
        ) cnt_orders_last_month
    from mytable
) t
group by order_month

The logic lies in the range specification of the window function; it orders record by month, and counts how many orders the customer placed last month. Then all that is left to do is aggregate and count distinct users.

Demo on DB Fiddle