Find number of regular users and how long they tend to be users

[Staggan]

Hello

 

I am trying to work out how many regular users I have to my site and how long those users tend to be users..

 

So, I have a table that logs every time a user visits my site and logs in, it stores the date / time as a unix timestamp and it logs their user id.

 

I started by getting the id's of any user who logs in more than 5 times in a specified period, but now I want to extend that...

 

 
SELECT userID as user, count(userID) as logins FROM login_history where timestamp > UNIX_TIMESTAMP('2014-06-01 00:00:00') and timestamp < UNIX_TIMESTAMP('2014-07-01 00:00:00') group by user having logins > 5;
 

 

So ideally a number of users in each month that visited over x times, users who visited at least x times for 3 months, 6 months, 9 months, 12 months..

 

 

Any suggestions would be appreciated 

Thanks