Sql query that selects all inactive users

Question

I have a query to implement.

I have 2 tables: user and login_logs table.

The user table looks like this:

id || first_name || last_name ||           email                 || username || activated || suspended

1 ||       John       ||      Dan       ||      john@whatever       ||      john1    ||     1      ||       0       ||

2 ||       Mike       ||      Hutt       ||      mike@whatever       ||      mike1    ||     1      ||       0       ||

etc.

The login_logs table looks like this:

id    ||     login_datetime     || user_id

1     || 2011-01-27 23:04:59 ||      1

2     || 2010-01-27 23:04:59 ||      2

etc.

So the login_logs table keeps record of when a user has logged in.

Now I want to make a query that selects all inactive users. Inactive users are:

1) users that have not logged in for 90 days

2) users that have never logged in

I have made a query that satisfies the first condition but is not completely correct:

 SELECT DISTINCT u.id, u.last_name, u.first_name, u.email,u.username

 FROM users u INNER JOIN login_logs l ON l.user_id = u.id 

 WHERE u.activated = 1 AND u.suspended = 0  AND DATEDIFF(CURDATE(), l.login_datetime) <= 90

 ORDER BY u.last_name, u.first_name, u.id

The above query is not right because if I have logged in ages ago, but also recently it treats me as an inactive user because it sees I logged in ages ago.

So I want to fix the mistake I describe above and also satisfy the second condition (Select people that have never logged in).

Answer 1

First your query needs to include all users, so turn it to a LEFT JOIN.

Next your query needs to use a GROUP BY to get the MAX login time per user.

Thirdly, don't put a date function against a column - it doesn't help performance, instead index the date column and construct a date to test against.

SELECT u.id, u.last_name, u.first_name, u.email, u.username
FROM users u
LEFT JOIN login_logs l ON l.user_id = u.id
WHERE u.activated = 1 AND u.suspended = 0
GROUP BY u.id, u.last_name, u.first_name, u.email, u.username
HAVING IFNULL(max(l.login_datetime), 0) <= ADDDATE(CURDATE(), interval -90 day)
ORDER BY u.last_name, u.first_name, u.id

The last part in there IFNULL(max(l.login_datetime), 0) ensures that if the LEFT JOIN resulted in no record in login_logs (never logged in), it will use the date 0 to test instead, which is obviously <= 90 days ago; therefore the record (user) shows.