mysql - Users that haven't logged in during last n months -

i remove users haven't logged in given period, period choosen me later based on results.

so need report in receive number of users haven't logged in, during last 1 month, 2 months ... n months.

i don't quite know how achive moving period in single mysql query.

extracting these users given period easy:

select count(distinct id) tmp_last_login  last_login <= date_sub(now(), interval 1 month) 

but how achive such report n months?

expected result:

**interval**    **cnt** 1                 xxx 2                 xxx 3                 xxx 4                 xxx 5                 xxx ...               xxx n                 xxx 

the following query return count of distinct ids each month difference within interval of 10 months now.

select 12 * (year(curdate()) - year(last_login)) + (month(curdate()) - month(last_login)) months, count(distinct id) cnt     tmp_last_login   last_login >= date_sub(curdate(),   interval 10 month) group months; 

or

you can use period_diff function well.

select period_diff(date_format(curdate(),'%y%m'),date_format(last_login,'%y%m')) months, count(distinct id) cnt     tmp_last_login   last_login >= date_sub(curdate(),   interval 10 month) group months; 

---

note:

if have multiple last_login entries same user need find maximum last login time each user , rest.

select period_diff(date_format(curdate(),'%y%m'),date_format(t.max_last_login,'%y%m')) months, count(t.id) cnt     (             select                  id,                 max(last_login) max_last_login             tmp_last_login             group id         ) t   t.max_last_login >= date_sub(curdate(),   interval 10 month) group months; 

sql fiddle demo