MySql Query : How to select data which is not exist in last 90 days from the given date value?

Question

I have one "User" table with fields UserId and addedDate.

I need to get data of users - added in the last month and current month and not in system for 90 days prior to added date.

User Table

Userid      addedDate         enddate
001        2017-07-01       2017-09-05
002        2017-02-25       2017-02-01
003        2017-06-01       2017-09-21
003        2017-04-25       2017-05-29
004        2017-06-01       2017-09-21  
005        2017-06-05       2017-09-07
005        2017-01-01       2017-01-31

I need output like :

userid
  001
  004
  005

Here 003 is not required as its not a new user(as its available system for 90 days prior to added date). and 002 is not added in last moth or current moth, so this also cant consider as new user.

I have tried below query but its not working fine :

select userid from usertbl final
WHERE final.userid NOT IN (
SELECT meb.userid 
FROM usertbl meb 
INNER JOIN 
( 
SELECT mc.userid, addeddate, enddate 
FROM usertbl mc 
WHERE mc.enddate > NOW() 
AND mc.addeddate > DATE_ADD(NOW(), INTERVAL -90 DAY) 
) s 
ON s.userid = meb.userid 
AND 
(
meb.enddate > DATE_ADD(s.addeddate,INTERVAL -90 DAY) 
AND meb.enddate <> s.enddate
) 
)

Please provide some suggestions.

Answer 1

You need to use INNER query to get the results:

SELECT *
FROM users
WHERE addedDate >= DATE_ADD(NOW(), INTERVAL -2 MONTH)
AND userid NOT IN (
    SELECT userid 
    FROM users
    WHERE addedDate < DATE_ADD(NOW(), INTERVAL -2 MONTH)
);

A couple of points:

• Userid 003 is not available 90 days prior to addedDate in the system (2017-06-01 vs 2017-04-25, that is 37 days), so it's probably same as selection criteria (i.e. not this month or last month)?
• Userid 005 is available 90 days (or last month) prior to addedDate so it should not be picked up as well

Here's the SQL Fiddle.