Mysql get number of loss users-CodePudding

I have a Users table (id, name, created_at) and a Transaction table(id, user_id, created_at, amount). For each month, I would like to know the number of users who did not have any transaction in the 3 months interval before that month. For example, for April 2022, the query would return number of users who did not have a transaction in January 2022, February 2022 and March 2022. And so on for every month.

Can I do this with a single MySQL query, and without PHP loop?

If I wanted it for April 2022 only, then I guess this would do the trick:

SELECT count(distinct(users.id)) FROM users 
INNER JOIN transactions 
    on users.id = transactions.user_id 
WHERE transactions.user_id NOT IN 
   (SELECT user_id FROM transactions WHERE created_at > "2022-01-01" AND created_at < "2022-04-01" );

How to get it for all months?

CodePudding user response：

SELECT count(*) 
FROM users 
WHERE NOT EXISTS (
    SELECT NULL
    FROM transactions 
    WHERE users.id = transactions.user_id 
      AND created_at > '2022-01-01' AND created_at < '2022-04-01'
    );

CodePudding user response：

In a normal situation, you would have a calendar table that, for examples, stores all starts of months over a wide period of time, like calendar(start_of_month).

From there on, you can cross join the calendar with the users table to generate all possible combinations of months and customers (with respect to the user's creation time). The last step is to check each user/month tuple for transations in the last 3 months.

select c.start_of_month, count(*) as cnt_inactive_users
from calendar c
cross join users u 
where not exists (
    select 1 
    from transactions t 
    where t.user_id = u.id 
        and t.created_at >= c.start_of_month - interval 3 month
        and t.created_at <  c.start_of_month
)
where c.start_of_month >= '2021-01-01' and c.start_of_month < '2022-01-01'
group by c.start_of_month
order by c.start_of_month

This gives you one row per month that has at least one "inactive" customers,with the corresponding count.

You control the range of months over which the query applies with the where clause to the query (as an example, the above gives you all year 2021).