CODEWITHSUNDEEP
mysqlsql
Reddy
Reddy

Reputation: 1453

MySQL: Get all user's who have logged in before but not after certain date, not working as expected

I have following data and I have written a query to get all user's who have logged in before but not logged in after a given date:

+----+------------------+----------------------+
| id |    user_login    |         date         |
+----+------------------+----------------------+
|  1 |  longtwin        |  2018-03-29 22:15:56 |
|  2 |  admin           |  2018-03-29 22:16:05 |
|  3 |  steve           |  2018-06-29 22:19:45 |
|  4 |  robinbiundo     |  2017-03-29 22:56:13 |
|  5 |  shannon         |  2017-06-29 23:07:38 |
|  6 |  long            |  2017-04-29 23:40:58 |
|  7 |  longtwin        |  2017-04-29 23:41:57 |
|  8 |  long            |  2017-03-30 02:24:46 |
|  9 |  long            |  2017-03-30 03:11:48 |
| 10 |  abigailrashbaum |  2016-03-30 11:50:43 |
| 11 |  timothybrown    |  2016-03-30 16:25:59 |
| 12 |  timothybrown    |  2016-03-30 17:09:27 |
| 13 |  timothybrown    |  2015-03-30 17:28:44 |
| 14 |  timothybrown    |  2015-03-30 17:33:06 |
| 15 |  steve           |  2014-03-30 18:03:46 |
| 16 |  steve           |  2014-03-30 18:04:48 |
| 17 |  steve           |  201-03-30 19:16:59  |
+----+------------------+----------------------+

I wrote following SQL but the result is not expected, please correct me what is wrong with my SQL?

SELECT p.user_login, COUNT(p.user_login)
    FROM wp_login_log_backup p
    WHERE p.user_login NOT IN (
            SELECT u.user_login
                FROM wp_login_log_backup u
                WHERE u.date < '2017-04-07 00:00:00'
                GROUP BY u.user_login
    )
    GROUP BY p.user_login;

I should get only timothybrown, robinbiundo as that user didn't login after 2017-04-07 00:00:00

Upvotes: 0

Views: 233

Answers (2)

Nick
Nick

Reputation: 147146

You can select all users who logged in before a given date and then a NOT EXISTS query to determine those who have not also logged in since that date:

SELECT l1.user_login, COUNT(*) AS logins
FROM wp_login_log_backup l1
WHERE l1.date < '2017-04-07 00:00:00' 
  AND NOT EXISTS (SELECT *
                  FROM wp_login_log_backup l2
                  WHERE l2.user_login = l1.user_login
                    AND l2.date > '2017-04-07 00:00:00' )
GROUP BY l1.user_login

Output:

user_login      logins
abigailrashbaum 1
robinbiundo     1
timothybrown    4

Demo on dbfiddle

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use aggregation:

select user_login
from wp_login_log_backup llb
group by user_login
having max(date) < '2017-04-07';

Upvotes: 1

Related Questions