SQL: Get users not loggedIn in given date range

user_4770835 0
Viewed 157

I have a scenario with two tables in MySQL.

Table 1: Users

enter image description here

Table 2: login_history

enter image description here

I'm trying to come up with a query to get those users who were not logged In, in given time period, for example between 2017-09-25 AND 2017-10-2.

I tried to use the sub-query, but that query is quite slow. In the example, I have given dummy data, but actually two tables specially login_history has huge amount of data, thus sub-query is taking time.

mysql sql database
2 Answers

It would be something like this:

select u.*
from users u
where not exists (select 1
                  from logins l
                  where l.user_id = u.id and
                        l.login_at >= '2017-09-25' and
                        l.login_at <= '2017-10-02'
                 );

If this is slow, then try creating an index on logins(user_id, login_at).

Assuming you only want users who have logged in at some point, you could all try aggregation:

select l.user_id
from logins l
group by l.user_id
having sum(l.login_at >= '2017-09-25' and l.login_at <= '2017-10-02') = 0;

However, the not exists should be faster.

user_11440350

Couldnt you just use a basic query?

 SELECT * FROM USERS U
 JOIN login_history L
 ON U.id = L.user_id
 WHERE login_at NOT BETWEEN '2017-09-25' AND '2017-10-2'
user_84127840
Related