Farhan Tahir Farhan Tahir - 3 years ago 213
SQL Question

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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download