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).

RichardTheKiwi · Accepted Answer · 2011-03-02 10:28:31Z

8

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.

answered Mar 2, 2011 at 10:28

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mathew Over a year ago

I think it works quite nicely! Thank you very much cyberwiki! Though i have to study the query since i wasnt aware of the having clause! Thanks!

Sachin Shanbhag · Accepted Answer · 2011-03-02 10:28:48Z

0

select u.* from user u left outer join login l
on u.id = l.user_id
where l.user_id is null
or DATEDIFF(CURDATE(), l.login_datetime) <= 90

answered Mar 2, 2011 at 10:28

Sachin Shanbhag

55.7k11 gold badges92 silver badges103 bronze badges

2 Comments

Johann Blais Over a year ago

This will select the same user several times if he/she logged in more than once in the last 90 days.

mathew Over a year ago

Thanks Sachin! Now it selects users that have never logged in.. But what about the case that I have logged in ages ago, but also recently it treats me as an inactive user because it sees I logged in ages ago??

Collectives™ on Stack Overflow

Sql query that selects all inactive users

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related