CODEWITHSUNDEEP
mysql
Ben Holness
Ben Holness

Reputation: 2707

SQL Query to find users with no record within the last 30 days, but with a record within the last 90 days

I am trying to construct a query that will list all user ids in a table that have an entry within the last 90 days, but not within the last 30 days.

A super simplified version of the table would have three fields: RecordID (Primary Key, Auto increment), UserID (Foreign Key) and RecordDate (timestamp).

I know how to do this in two queries, but can it be done in one query? It should return the userid for any user with an entry less than 90 days old, but no entries in the last 30 days.

Upvotes: 3

Views: 4205

Answers (4)

Steve McLeod
Steve McLeod

Reputation: 52448

select distinct(userid) 
  from thetable 
 where recorddate > DATE_SUB(now(), INTERVAL 90 DAY)
   and userid not in 
      (
        select userid 
          from thetable 
         where recorddate > DATE_SUB(now(), INTERVAL 30 DAY)
      )

I don't know mySQL well, so use the appropriate way for checking if a date is within the last x days

I assume that a userid can be in the table multiple times, but you only want them returned once. Hence the "distinct"

Upvotes: 0

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74360

The following query should do the job:

SELECT UserID 
FROM MyTable AS T1
WHERE 
  (SELECT MIN(DATEDIFF(NOW(),RecordDate)) 
   FROM MyTable AS T2                           -- Most recent entry must be more
   WHERE T2.UserID=T1.UserId) BETWEEN 31 AND 90 -- than 30 days ago, but no more
                                                -- than 90 days ago

Upvotes: 1

Curt
Curt

Reputation: 5722

SELECT UserID
FROM
(
   SELECT UserID, 
          MIN(RecordDate) AS min_date,
          MAX(RecordDate) AS max_date
     FROM MyTable
          GROUP BY UserID
)
WHERE max_date <  AddDate(CURRENT_TIMESTAMP, INTERVAL -30 DAY)
  AND min_date >= AddDate(CURRENT_TIMESTAMP, INTERVAL -90 DAY)

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

This should do it

SELECT DISTINCT UserID 
FROM Table t1    
WHERE RecordDate > DATE_SUB(NOW(), INTERVAL 90 DAY)
AND NOT EXISTS (
  SELECT 1 FROM Table
  WHERE RecordDate > DATE_SUB(NOW(), INTERVAL 30 DAY)
  AND UserID = t1.UserID
)

Upvotes: 0

Related Questions