Retrieve a list of users who haven’t made any activity in the last x months

I just got an interesting question from an AppGini user:

Is there an SQL query that I can run manually in PhpMyAdmin that will show me a list of all users that haven’t logged into AppGini for the last 6 months please? Even if it is using one of the date fields in the membership_users table to show me a list of all users that haven’t added or changed a record for the last 6 months, that would suffice.

Well, AppGini doesn’t currently (until AppGini 5.72) store the login date/time of users (but we plan to add this in future releases). So, what we can do is run a query to retrieve non-active users (with no insert/edit activity in the last 6 months).

First, we need to create a new view in MySQL to list users’ last activity:

CREATE VIEW members_activity AS 
select 
    u.memberID as 'memberID', 
    u.signupDate, 
    max(FROM_UNIXTIME(r.dateAdded)) as 'last_insert', 
    max(FROM_UNIXTIME(r.dateUpdated)) as 'last_update'
from 
    membership_users u left join 
    membership_userrecords r on u.memberID=r.memberID
group by u.memberID

CREATE VIEW members_activity AS

select

u.memberID as 'memberID',

u.signupDate,

max(FROM_UNIXTIME(r.dateAdded)) as 'last_insert',

max(FROM_UNIXTIME(r.dateUpdated)) as 'last_update'

from

membership_users u left join

membership_userrecords r on u.memberID=r.memberID

group by u.memberID

Now, you can use the view ‘members_activity’ that we defined above like you use any table (except it’s not actually a table, but it makes our life easier when performing complex queries). So, to retrieve all users who have no insert/update activity or whose last insert/update activity is more than 6 months old:

select * from members_activity where
	(isnull(last_insert) and isnull(last_update)) or
	(
		last_insert < now() - interval 6 month and
		last_update < now() - interval 6 month
	)

select * from members_activity where

(isnull(last_insert) and isnull(last_update)) or

(

last_insert < now() - interval 6 month and

last_update < now() - interval 6 month

)

But the above query might retrieve new users who joined less than 6 months ago … to exclude those, we should add another condition:

select * from members_activity where
	signupDate < now() - interval 6 month and (
		(isnull(last_insert) and isnull(last_update)) or
		(
			last_insert < now() - interval 6 month and
			last_update < now() - interval 6 month
		)
	)

select * from members_activity where

signupDate < now() - interval 6 month and (

(isnull(last_insert) and isnull(last_update)) or

(

last_insert < now() - interval 6 month and

last_update < now() - interval 6 month

)

Published by Genedy