CODEWITHSUNDEEP
mysqlsqldatabase
Farhan Tahir
Farhan Tahir

Reputation: 2134

SQL: Get users not loggedIn in given date range

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.

Upvotes: 3

Views: 517

Answers (2)

Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

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'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

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.

Upvotes: 1

Related Questions